Retrieve ASCII Code Value
ASCII ('A') = 65
ASCII ('BEE') = 66
Convert Integer ASCII Code to Character
CHAR (65) = 'A' CHAR (1000) = NULL
Search for a Character
CHARINDEX ('A', 'B', [, S])
CHARINDEX ('Z', 'HELLO') = 0
CHARINDEX ('H', 'HELLO') = 1
CHARINDEX ('OR', 'WORLD') = 2
CHARINDEX ('L', 'HELLO', 4) = 4
Replace a 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
Using STUFF to Replace Substring
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 Function
-- Returns a four-character SOUNDEX code for string similarity
SOUNDEX ('Smith') = 'S530'
SOUNDEX ('Smythe') = 'S530'
DIFFERENCE ('A', 'B') of Soundex
-- Calculates the difference between two soundex codes
DIFFERENCE('GREEN','GREENE') = 4
LEN Function - Length of a String
LEN ('HELLOW WORLD') = 11
LEN ('HELLOW ') = 6
LEN (12) = 2
LEN ('') = 0
Extract a Substring
-- Returns L characters starting at position S in string A
SUBSTRING('HELLOW',2,1) = 'E'
SUBSTRING('HELLOW',5,10) = 'OW'
SUBSTRING('HELLOW',10,1) = ''
SUBSTRING('HELLOW',0,1) = ''
SUBSTRING('HELLOW',1,0) = ''
Extract Right/Left Characters
RIGHT ('', 1) = ''
LEFT ('HI', 0) = ''
RIGHT ('HI', 3) = 'HI'
RIGHT ('HELLOW WORLD',5) = 'WORLD'
LEFT ('HELLOW WORLD', 6) = 'HELLOW'
Insert Spaces
SPACE(5) = ' '
REPLICATE Function
REPLICATE ('*', 4) = '****'
REPLICATE ('-', 0) = ''
REPLICATE ('^', NULL) = NULL
Convert 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 Function
CONCAT('HELLO')=ERROR
CONCAT(NULL, NULL)= ''
CONCAT('Hi',' ','Patel')='Hi Patel'
CONCAT(12,NULL,34) = '1234'
CONCAT(2014,12,31) = '20141231'
Case Change Functions
LOWER ('SQL') = 'sql'
UPPER ('hi') = 'HI'
Trim Whitespace from Strings
LTRIM (' HI ') = 'HI '
LTRIM (' ') = ''
RTRIM(' HI ') = ' HI'
QUOTENAME Function
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 Function
| REVERSE('HELLOW') = 'WOLLEH' REVERSE(12) = 21 |
