Sql Server String Functions Cheat Sheet
ADVERTISEMENT
1CHARACTER CODES
STRING EXTRACTION
STRING MANIPULATION
('%pattern%' , ‘A’)
PATINDEX
(‘C’)
(‘A’, ‘B’ [,‘C’])
ASCII
CONCAT
(‘A’, start, length)
SUBSTRING
Returns starting position of the first occurrence
Returns the ASCII code value of the leftmost
Returns a string that is the result of
of a %pattern% in string ‘A’
Returns L characters of ‘A’ starting at S
character of ‘С’
concatenating two or more string values
PATINDEX('H','HELLO') =
0
SUBSTRING('HELLOW',2,1) =
'E'
ASCII
('A') =
65
CONCAT('HELLO')=ERROR
PATINDEX('H%','HELLO') =
1
SUBSTRING('HELLOW',5,10) =
'OW'
CONCAT(NULL, NULL)=
''
ASCII
('BEE') =
66
PATINDEX('%L_%','HELLO') =
3
SUBSTRING('HELLOW',10,1) =
''
CONCAT('HI',' ','WORLD')='HI WORLD'
PATINDEX('%L_','HELLO') =
4
SUBSTRING('HELLOW',0,1) =
''
CONCAT(12,NULL,34) =
'1234'
CHAR
(A)
PATINDEX
('Z','HELLO') =
0
SUBSTRING('HELLOW',1,0) =
''
CONCAT(2014,12,31) =
'20141231'
PATINDEX('%A[0-9]%','AA1A') =
2
Converts an integer ASCII code A to a character
(‘A’, B)
(‘A’, B)
PATINDEX('%L[^L]%','HELLO') =
4
LEFT
/
RIGHT
CHAR
(65) =
'A'
(‘A’) /
(‘A’)
LOWER
UPPER
Returns the left/right part of ‘A’ with the
CHAR
(1000) = NULL
(‘A’, S, L, ‘B’)
STUFF
specified number B
Makes ‘A’ lowercase/uppercase
Replaces L characters of ‘A’ starting at S with ‘B’
RIGHT
('', 1) =
''
UNICODE
(A)
LOWER
('HI') =
'hi'
LEFT
('HI', 0) =
''
STUFF('HELLOW',2,5,'I') =
'HI'
UPPER
('hi') =
'HI'
Returns the int value for the first character of ‘A’
RIGHT
('HI', 3) =
'HI'
STUFF('HELLOW',2,15,'I') =
'HI'
UNICODE('A') =
65
RIGHT
('HELLOW
WORLD',5) =
'WORLD'
STUFF('HELLOW',20,1,'I') = NULL
(‘A’) /
(‘A’)
LTRIM
RTRIM
LEFT
('HELLOW
WORLD', 6) =
'HELLOW'
STUFF('HELLOW',0,1,'I') = NULL
NCHAR
(A)
STUFF('HELLOW',1,0,'I') =
'IHELLOW'
Returns ‘A’ removing leading/trailing blanks
STRING GENERATION
Returns the Unicode character with the specified
SOUNDEX & OTHER
LTRIM
(' HI
') =
'HI '
integer code A
LTRIM
('
') =
''
SPACE
(A)
NCHAR
(66000) = NULL
RTRIM(' HI
') =
' HI'
(‘A’)
SOUNDEX
NCHAR
(8) =
''
Returns a string of A spaces
NCHAR
('8') =
''
(‘A’, [‘B’])
QUOTENAME
Returns a four-character (SOUNDEX) code to
SPACE(2) =
'
'
evaluate the similarity of two strings
SEARCH & REPLACE
Makes ‘A’ a valid SQL Server using ‘B’ delimiter
(‘A’, B)
REPLICATE
SOUNDEX
('Smith') =
'S530'
QUOTENAME('TB NAME')=[TB NAME]
SOUNDEX
('Smythe') =
'S530'
Repeats a string value ‘A’ specified number of
QUOTENAME('TB NAME', '] ')=[TB NAME]
(‘A’, ‘B’, [, S])
CHARINDEX
times B
QUOTENAME('TB NAME', '"')="TB NAME"
(‘A’, ‘B’)
Searches ‘B’ for ‘A’ and returns its starting
DIFFERENCE
QUOTENAME('abc[]def')=[abc[]]def]
REPLICATE ('0',
4) =
'0000'
position if found. The search starts at S
QUOTENAME('TB NAME', '''')='TB NAME'
REPLICATE ('-',
0) =
''
Returns an integer value that indicates the
('Z', 'HELLO') =
CHARINDEX
0
REPLICATE
('-', NULL)
=
NULL
difference between the SOUNDEX of ‘A’ and ‘B’
CHARINDEX
('H', 'HELLO') =
1
(‘A’)
DIFFERENCE('GREEN','GREENE') =
4
REVERSE
('OR', 'WORLD') =
CHARINDEX
2
STR
(A [,length [, decimal]])
Returns the reverse order of a string value
('L', 'HELLO', 4) =
CHARINDEX
4
(‘A’)
Converts A number to string
LEN
REVERSE('HELLOW') =
'WOLLEH'
STR
(2.234) =
'
2'
(‘A’, ‘B’, ‘C’)
REPLACE
REVERSE(12) =
21
Returns length of ‘A’, excluding trailing blanks
STR
(2.234, 4) =
'
2'
STR
(2.234, 4, 2) =
'2.23'
LEN
('HELLOW
WORD') =
11
Replaces in ‘A’ all occurrences of string ‘B’ with
STR
(2.234, 6, 2) =
'
2.23'
LEN
('HELLOW
') =
6
string ‘С’
LEN
(12) =
2
LEN
('') =
0
REPLACE('HELLOW',NULL,'')=NULL
REPLACE('HELLOW','','_')='HELLOW'
REPLACE('HELLOW','ELLOW','I')='HI'
REPLACE('HELLOW','L',1) =
'HE11OW'
ADVERTISEMENT
0 votes
Related Articles
Related forms
Related Categories
Parent category: Education