SQL Server String functions

SQL Server String Functions are:

Function Description Example Result
ASCII(expression) Returns ASCII code value of the leftmost character of expression ASCII(‘pinnapa’) 112
CHAR(integer_expression) Converts int ASCII code to a character CHAR(65) A
CHARINDEX Searches expressions for another expression and returns its starting position character or word CHARINDEX(Expresstofind, expressiontosearch)
CHARINDEX(‘in’,’pinnapa’)
1
CONCAT Used to add more than one string CANTAT(‘pinnapa’,’ a place to ‘,’learn’) pinnapa a place to learn
DIFFERENCE(expr1,expr2) Outputs the integer value that indicates the difference between SOUNDEX values of two expressions DIFFRENCE(‘pinnapa’,’pinnapa’) 4
LEFT(expression,n) Gives the left part of character expression with specified no of characters LEFT(‘pinnapa’,5) pinna
LEN(expression) Returns the number of characters of given string expression. LEN(‘pinnapa’) 7
LOWER(expr) Returns lowercase of given character expression. LOWER(‘PINNap.com’) pinnapa.com
LTRIM(expr) Removes the leading blanks of given expression LTRIM(‘ pinnapa.com’) pinnap.com
RTRIM Removes the all trailing blanks of given expression RTRIM(‘pinnapa.com ‘) pinnap.com
SPACE This function is used to generate spaces “hello”+space(5)+”World” Hello World
STR This function is used to return character data converted from numeric data STR(100.2334, 5, 2); 100.2
STUFF This function is used to another string SELECT STUFF(‘pinnapa.com’, 3, 2,’ppp’) pippapa.com
SUBSTRING Used to extractpart of string starting from specified character SELECT SUBSTRING(‘pinnapa.com’, 2,3) inn

Example:
SQL-Server-Insert1

How to get left three characters of empname, replace letter o with a and get substring starting from second position and there after 3 characters.
SELECT LEFT(empname,3) as empshort, REPLACE(location, ‘o’,‘a’) as locreplace, SUBSTRING(job, 2,3) as subjob from emp

Powered by k2schools