Frequently used SQL Server string functions quick reference

These string functions can be found in the SQL Server online book. I list them here to make it easier for me to lookup.

Function Description
LEFT(string, lenght)
RIGHT(string, length) Returns leftmost or rightmost of a string with given length.
LEN(string) Returns the length of a string.
LOWER(string)
UPPER(string) Returns string lower or upper case.
LTRIM(string)
RTRIM(string) Trim a string in left or right side. For a full trim, use LTRIM(RTRIM(string))
PATINDEX(pattern, string) Returns the starting position of the first instance of pattern in the string. If the pattern is not found, then returns zero. eg

select PATINDEX('%CAT%', 'CATALOGUE') as Match
Match
------------------
1

REPLACE(string to replace, for, with) Returns a string in which all occurrences if the second expression within the first expression are replaced with the third expression. eg.
SELECT REPLACE('ABCDE', 'AB', '***') as Result
-------------
***CDE
QUOTENAME(string, quote) Returns a string with quotation mark surrounded. The quote can only be ', ", or [ or ]. eg
SELECT QUOTENAME('hello', '[') as Result
---------------
[hello]
REPLICATE(string, times) Replicate a string with number of times.
SPACE(length) Return blanks with given length.
SUBSTRING(string, start, length)