SQL Server String Functions

String Functions

String functions are used to format data and mostly used with char and varchar data types.

String functions are used for various operations on strings and can be used as a part of character expressions.

String Functions Syntax

SELECT string_function(parameters)

here string_function is the string function name and parameters are the required arguments for the string function.

ASCII Function

ASCII function is used to get the ASCII value for left most character in a string.

SELECT ASCII('CDE')

67

returns ASCII value for character 'C'.

CHAR Function

CHAR function is used to get the character for the ASCII value.

SELECT CHAR(67)

C

returns character for given ASCII value.

CHARINDEX Function

CHARINDEX function is used to get the starting index for a pattern string on the string.

SELECT CHARINDEX('OD', 'CODINGPOINDER')

2

returns the starting index of pattern string 'OD' on string 'CODINGPOINTER'.

CHARINDEX function returns zero if not found a pattern on the string.

SELECT CHARINDEX('CD', 'CODINGPOINDER')

0

LOWER Function

LOWER function is used to get the string in lower case letters.

SELECT LOWER('CodingPointer')

codingpointer

returns the string in lower case.

UPPER Function

UPPER function is used to get the string in upper case letters.

SELECT UPPER('CodingPointer')

CODINGPOINTER

returns the string in upper case.

LEFT Function

LEFT function is used to get the sub string from left based on number of character specified.

SELECT LEFT('CodingPointer', 6)

Coding

returns the 6 characters from left 'Coding' on the string 'CodingPointer'.

RIGHT Function

RIGHT function is used to get the sub string from right based on number of character specified.

SELECT RIGHT('CodingPointer', 7)

Pointer

returns the 7 characters from right 'Pointer' on the string 'CodingPointer'.

LEN Function

LEN function is used to get the number of characters on the string.

SELECT LTRIM('CodingPointer')

13

returns the 13 which total number of characters on the string 'CodingPointer'.

LTRIM Function

LTRIM Function is used to remove the leading blanks(empty spaces) from the string.

SELECT LTRIM('  CodingPointer')

CodingPointer

returns the leading empty spaces on the string ' CodingPointer'.

RTRIM Function

RTRIM Function is used to remove the trailing blanks(empty spaces) from the string.

SELECT RTRIM('CodingPointer  ')

CodingPointer

returns the trailing empty spaces on the string 'CodingPointer '.

REVERSE Function

REVERSE function is used to get the string in reverse which is from right to left.

SELECT REVERSE('CODINGPOINDER')

REDNIOPGNIDOC

PATINDEX Function

PATINDEX function is used to get the starting index of the specified pattern on the string, returns zero if pattern is not found.

SELECT PATINDEX('%GPOI%', 'CODINGPOINDER')

6

SUBSTRING Function

SUBSTRING function is used to get the sub string based on specified index and number of characters on the string.

SELECT SUBSTRING('CODINGPOINDER', 2, 5)

ODING

STR Function

STR function is used to convert the numeric data into string and can also specify number of decimals and total number of characters in the string.

SELECT STR(45233.42378, 9, 3)

45233.424

STUFF Function

STUFF function is used to replace the string on the string using starting index and number of characters to be deleted and inserting sub string.

SELECT STUFF('CODINGPOINDER', 7, 7, ' POINTER')

CODING POINTER

SPACE Function

SPACE function is used to add number of spaces required on the string.

SELECT 'CODING' + SPACE(1) + 'POINDER'

CODING POINTER

DIFFERENCE Function

DIFFERNCE Function is used to compare two strings and provides similarity between them (match ranges 0 to 4).

4 -> best match, 0 -> no match

SELECT DIFFERENCE('CODINGPOINDER', 'CODINGPOINDER')

4