This post is about which string functions there are in Transact-SQL. They allow to modify data you have, make custom queries to select records from database tables. To know them is very important for any developer - using string functions is very good way to decrease load from application server without adding it to SQL because string functions on SQL server work very fast.

Let's declare and set some variables. We'll use them below some times.

declare @str varchar(50) = 'My name is Alexey ' 
declare @ltrim varchar(50) = ' and I''m 34' 

List of functions:

 

ASCII

ASCII (char) or ASCII (varchar) returns ASCII code value of the first incoming character. Since MS SQL Server 2005

SELECT ASCII(@str) as asciicode
The result of this is: 77

 

CHAR

CHAR (int ASCII code value) - returns character of ASCII code value. Since MS SQL Server 2005
SELECT CHAR(77)

SELECT CHAR(CONVERT(int, (SELECT ASCII(@str) as asciicode))) as charval

The same result of both these queries is: M

 

CHARINDEX

CHARINDEX(stringToFind, stringToSearchIn [ , start_position_of_search ])- returns the position of the first incoming. Since MS SQL Server 2005
SELECT CHARINDEX('Alexey', 'My name is Alexey') as charindexval

The result is: 12

 

CONCAT

CONCTAT (string_value_1, string_value_1 [,string_value_N]) - concatenate strings, since MS SQL Server 2012
SELECT CONCAT (@str, @ltrim)

The result is: My name is Alexey  and I'm 34

 

LEFT

LEFT (character_string, int_value) - returns int_value characters from character_string from Left to Right. UTF-16 surrogate character is one character. Since MS SQL Server 2005
SELECT LEFT('My name is Alexey', 10) as leftstr

The result of this is: 'My name is'

 

RIGHT

RIGHT (character_string, int_value) - returns int_value characters from character_string from Right to Left. UTF-16 surrogate character is one character. Since MS SQL Server 2005
SELECT RIGHT ('My name is Alexey', 6) as rightstr

The result is: 'Alexey'

 

LEN

LEN (character_string) - returns the length of character_string as (big)int value
SELECT LEN ('My name is Alexey') as length

The result is: 17

 

LOWER

LOWER (character_string) - returns a character string in lowercase
SELECT LOWER('My name is Alexey') as lowc

The result is: 'my name is alexey'

 

UPPER

UPPER (character_string) - returns a character string in uppercase
SELECT  UPPER('My name is Alexey') as upc

The result is: 'MY NAME IS ALEXEY'

 

LTRIM

LTRIM (character_string) - returns a string without spaces from the left. It's good to see the example of LTRIM on cocatecating of strings
SELECT ('My name is Alexey ') + LTRIM('     and I''m 34')

The result is 'My name is Alexey and I'm 34'. There are no 5 spaces before 'and'

 

RTRIM

RTRIM (character_string) - returns a string without spaces from the RIGHT. It's good to see the example of RTRIM on cocatecating of strings
SELECT RTRIM('My name is Alexey         ') + (' and I''m 34')

The result is: 'My name is Alexey and I'm 34'. There are no 5 spaces after 'Alexey'

 

NCHAR

NCHAR (int UNICODE value) - is similar to CHAR, but returns the character of UNICODE value. Since MS SQL Server 2005
SELECT NCHAR(9783)

The result is: ☷

 

PATINDEX

PATINDEX ('%pattern%', expression) - Returns the starting position of the first occurrence of a pattern in the expression, or zeros if the pattern is not found, on all valid text and character data types. Symbols '%' should be before and after pattern, but if you want to look for pattern in the end of expression, it used to be like '%pattern' with only '%' before. Since MS SQL Server 2008
SELECT PATINDEX('%lex%', 'My name is Alexey')

The result is: 13

 

QUOTENAME

QUOTENAME('character_string' [ , 'quote_character' ]) Returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier. Since MS SQL Server 2008
SELECT QUOTENAME('abc]def', '''')

The result is: 'abc]def'

 

REPLACE

REPLACE ( string_expression , string_pattern , string_replacement ) - Replaces all occurrences of a specified string value with another string value. Since MS SQL Server 2008
SELECT REPLACE('My name is Alexey', 'Alexey', 'Mark')

The result is 'My name is Mark'

 

REPLICATE

REPLICATE (string, int_times)  Repeats a string value a specified number (int_times) of times. Since MS SQL Server 2008
SELECT REPLICATE('Alexey ', 3)

The result is 'Alexey Alexey Alexey '

 

REVERSE

REVERSE (string) - Returns the reverse order of a string value. Since MS SQL Server 2008
SELECT REVERSE('Alexey')

The result is: yexelA

 

SPACE

SPACE (int) - Returns int spaces. It is good to see in concatecation of strings. Since MS SQL Server 2008
SELECT '[' + SPACE (10) + ']'

The result is: [          ] (10 spaces between brackets)

 

STR

STR ( float_expression [ , length [ , decimal ] ] ) - Returns character data converted from numeric data. Since MS SQL Server 2008
SELECT STR(113.44, 5, 1)

The result is 113.4

 

STUFF

STUFF ( character_expression , start , length , replaceWith_expression )  - The STUFF function inserts a string into another string. It deletes a specified length of characters(length) in the first string at the start position and then inserts the second string into the first string at the start position.
SELECT STUFF('Alexey', 2, 0, 'argument')

The result is 'Aargumentlexey'

 

SUBSTRING

SUBSTRING ( expression ,start , length ) - Returns part of a character, binary, text, or image expression in SQL Server.
SELECT SUBSTRING('Alexey', 1, 4)

The result is Alex

 

UNICODE

UNICODE ( 'ncharacter_expression' ) - Returns the integer value, as defined by the Unicode standard, for the first character of the input expression.
SELECT UNICODE('Å')

The result is: 65