top of page
    • 2 min read

SSMS - String Functions Cheat Sheet

Here are some of the common string functions. It has helped me as a beginner to reference when I am converting strings. The list will expand from time to time, as I add new things... So please bookmark this page if you find it useful! In humor, I've also slapped the name of my website all over the examples. I hope you don't mind! :-)


CONCAT(STRING_1, STRING_2,STRING..._N) - returns a string resulting from the concatenation, or joining, of two or more string values in an end-to-end manner

E.g.:. CONCAT('ABC','123')

Result: 'ABC123'


LEN(STRING) - returns numbers of characters. Includes leading spaces, but not trailing

E.g.: LEN(' LearnwithGlezy ')

Result: 15


LTRIM(STRING) - any leading spaces is removed

E.g.: RTRIM(' LearnwithGlezy ')

Result: 'LearnwithGlezy '


RTRIM(STRING) - any trailing spaces is removed

E.g.: RTRIM(' LearnwithGlezy ')

Result: ' LearnwithGlezy'


LEFT(STRING,LENGTH) - returns specified number of characters from the beginning of the string

E.g.: LEFT('LearnwithGlezy',5)

Result: ' LearnwithGlezy'


RIGHT(STRING,LENGTH) - returns specified number of characters from the end of the string

E.g. RIGHT('LearnwithGlezy',5)

Result: 'Glezy'


SUBSTRING(STRING,START,LENGTH) - returns specified number of characters

E.g.: SUBSTRING('LearnwithGlezy',6,4)

Result: 'with'


REPLACE(SEARCH,FIND,REPLACE) - returns the search string with all occurrences of the find string replaced with the "new" string

E.g.: REPLACE('LearnwithGlezy','Learn','Play')

Result: 'PlaywithGlezy'


REVERSE(STRING) - returns the characters in reverse order

E.g.: REVERSE('LearnwithGlezy')

Result: 'yzelGhtiwnraeL'


CHARINDEX(FIND,SEARCH[,START]) - returns an integer that represents the position of the first occurrence of the FIND STRING in the SEARCH STRING starting at the specified position. If the START position isn't specified, the search starts at the beginning of the string. If the string isn't found, the function returns 0

E.g.: CHARINDEX('le','LearnwithGlezy')

Result: 2 -- (LEarnwithgLEzy)


PATINDEX(FIND,SEARCH[,START]) - returns an integer that represents the position of the first occurrence of the FIND PATTERN in the SEARCH STRING starting at the specified position. If the starting position isn't specified, the search starts at the beginning of the string. If the pattern isn't found, the function returns 0. The find pattern can include wildcard characters. If the patter begins with a wildcard, the value returned is the position of the first non-wild character

E.g.: PATINDEX('%glezy%', 'LearnwithGlezy')

Result: 10 -- (LearnwithGlezy)


LOWER(STRING) - returns the string converted into lowercase letters

E.g.: LOWER('LearnwithGlezy')

Result: 'learnwithglezy'


UPPER(STRING) - returns the string converted to uppercase letters

E.g.: UPPER('LearnwithGlezy')

Result: 'LEARNWITHGLEZY'


SPACE(INTEGER) - returns a string with the specified number of space characters (blanks)

E.g.: SPACE(5)

Result: ' '




0 comments

Comments

bottom of page