Returns the ASCII code value
ASCII ('A') = 65
ASCII ('BEE') = 66
Converts an integer ASCII code A to a character
CHAR (65) = 'A' CHAR (1000) = NULL
Search character
CHARINDEX (‘A’, ‘B’, [, S])
CHARINDEX ('Z', 'HELLO') = 0
CHARINDEX ('H', 'HELLO') = 1
CHARINDEX ('OR', 'WORLD') = 2
CHARINDEX ('L', 'HELLO', 4) = 4
Replace Character
REPLACE (‘A’, ‘B’, ‘C’)
REPLACE('HELLOW',NULL,'')=NULL
REPLACE('HELLOW','','_')='HELLOW'
REPLACE('HELLOW','ELLOW','I')='HI'
REPLACE('HELLOW','L',1) = 'HE11OW'
PATINDEX (‘%pattern%’ , ‘A’)
PATINDEX('H','HELLO') = 0
PATINDEX('H%','HELLO') = 1
PATINDEX('%L_%','HELLO') = 3
PATINDEX('%L_','HELLO') = 4
PATINDEX ('Z','HELLO') = 0
PATINDEX('%A[0-9]%','AA1A') = 2
PATINDEX('%L[^L]%','HELLO') = 4
STUFF (‘A’, S, L, ‘B’)
STUFF('HELLOW',2,5,'I') = 'HI'
STUFF('HELLOW',2,15,'I') = 'HI '
STUFF('HELLOW',20,1,'I') = NULL
STUFF('HELLOW',0,1,'I') = NULL
STUFF('HELLOW',1,0,'I') = 'IHELLOW'
STUFF('HELLOW',2,5,'I123456') = 'HI123456'
STUFF('HELLOW ',2,2,'I') = HILOW
SOUNDEX
--Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings
SOUNDEX ('Smith') = 'S530'
SOUNDEX ('Smythe') = 'S530'
DIFFERENCE (‘A’, ‘B’) of soundex
--Returns an integer value that indicates the difference between the SOUNDEX
DIFFERENCE('GREEN','GREENE') = 4
LEN Returns length of ‘A’, excluding trailing blanks
LEN ('HELLOW WORD') = 11
LEN ('HELLOW ') = 6
LEN (12) = 2
LEN ('') = 0
SUBSTRING
–Returns L characters of ‘A’ starting at S
SUBSTRING(‘HELLOW’,2,1) = ‘E’
SUBSTRING(‘HELLOW’,5,10) = ‘OW’
SUBSTRING(‘HELLOW’,10,1) = ”
SUBSTRING(‘HELLOW’,0,1) = ”
SUBSTRING(‘HELLOW’,1,0) = ”
Right / Left
RIGHT ('', 1) = ''
LEFT ('HI', 0) = ''
RIGHT ('HI', 3) = 'HI'
RIGHT ('HELLOW WORLD',5) = 'WORLD'
LEFT ('HELLOW WORLD', 6) = 'HELLOW'
Space
SPACE(5) = ' '
REPLICATE
REPLICATE (‘*’, 4) = ‘****’
REPLICATE (‘-‘, 0) = ”
REPLICATE (‘^’, NULL) = NULL
Converts A number to string
STR (2.234) = ' 2' STR (2.234, 4) = ' 2' STR (2.234, 4, 2) = '2.23' STR (2.234, 6, 2) = ' 2.23' STR (123456.789,4,2) = '****' STR(123456.789,14,2) = ' 123456.79'
CONCAT
CONCAT('HELLO')=ERROR
CONCAT(NULL, NULL)= ''
CONCAT('Hi',' ','Patel')='Hi Patel'
CONCAT(12,NULL,34) = '1234'
CONCAT(2014,12,31) = '20141231'
Case Change
LOWER ('SQL') = 'sql'
UPPER ('hi') = 'HI'
Trim string
LTRIM (' HI ') = 'HI '
LTRIM (' ') = ''
RTRIM(' HI ') = ' HI'
QUOTENAME
QUOTENAME('H NAME') = [TB NAME]
QUOTENAME('H NAME', '] ') =[H NAME]
QUOTENAME('H NAME', '"') = "H NAME"
QUOTENAME('abc[]dff') = [abc[]]dff]
QUOTENAME('TB NAME', '''')='TB NAME'
REVERSE
| REVERSE(‘HELLOW’) = ‘WOLLEH’ REVERSE(12) = 21 |
