SQL String Functions

SQL (Structured Query Language) provides a set of data types and various operations associated with each of these data types. One of these data types is string. There are many SQL string functions to operate upon string objects.

String Functions in SQL

Before beginning to learn about the various string operations, let us create a dummy table in our SQL database. This can be done as follows:

String manipulation is a common and important operation in programming. SQL provides us with a host of useful functions for performing many diffeent types of operations on strings.

Following are the string functions defined in SQL:

1. ASCII

ASCII function is used for obtaining the ASCII value of the first (leftmost) character of a string object in SQL.

Syntax

Example

Output

2. CHAR

CHAR function returns the character corresponding to a number(s) passed as argument(s) to it.

Syntax

Example

Output

3. CHARINDEX

CHARINDEX returns the position of a character in a string.

Syntax

Example

Output

4. CONCAT

CONCAT is used to concatenate (join) 2 expressions.

Syntax

Output

Example

Output

5. CONCAT with +

We can also append expressions after another using plus (+) operator.

Syntax

Example

Output

6. CONCAT_WS

CONCAT_WS is used for joining multiple strings together with a common separator at each join.

Syntax

The first argument is the separator string that will be placed between each 2 strings. The other args are strings to concatenate.

Example

Output

7. DATALENGTH

DATALENGTH is used to obtain the length of a string.

Syntax

Example

Output

8. DIFFERENCE

DIFFERENCE is used to compare 2 SOUNDEX values, and return an integer. The integer value indicates the match for the two SOUNDEX values, ranging from 0 to 4.

0 implies no or weak similarity between the 2 SOUNDEX values. 4 indicates identically same or strong similarity in SOUNDEX values.

Syntax

Example

Output

9. FORMAT

FORMAT function is used to format a value with a specified format.

Syntax

val is the expression to be formatted and format is the format pattern to be used.

Example

Output

10. LEFT

LEFT is used obtain a desired number of characters from the start of a string.

Syntax

Example

Output

11. LEN

LEN is used to obtain the length of a string.

Syntax

Example

Output

12. LOWER

LOWER is used to convert a string to lower case.

Syntax

Example

Output

13. LTRIM

LTRIM is used to remove spaces from the beginning of a string.

Syntax

Example

Output

14. NCHAR

NCHAR function returns the unicode character corresponding to the number code passed as argument.

Syntax

Example

Output

15. PATINDEX

PATINDEX is used to obtain the position of a pattern in a string. If the pattern is not present in the string, the function returns 0. The searching done is case-insensitive.

Syntax

PTRN and STR are the patterns and the string, respectively. The pattern must begin and end with % character. Other wildcards can be used in pattern, such as %, _, [], [^], etc.

Example

Output

16. QUOTNAME

The QUOTENAME() function takes in as arguments 2 strings and returns a unicode string with delimiters added to make the string a valid SQL delimited identifier.

The first argument is the string to be delimitted, and the second argument is the string to be used delimitter.

If the second argument is not provided or is an empty string, brackets are used by default.

Syntax

str is the string and the del is the delimiter to be used (optional).

Example

Output:

17. REPLACE

REPLACE function is used to replace the occurrences of a substring with another string. The function operates in a case-sensitive manner.

Syntax

S1 -> string of which S2 is a substring of.
S2 -> substring to be replaced.
S3 -> the string with which S2 will be replaced.

Example

Output

18. REPLICATE

REPLICATE is used to repeat a string a certain number of times.

Syntax

Example

Output

19. REVERSE

REVERSE function reverses the order in which the characters of a string occur.

Syntax

Example

Output

20. RIGHT

RIGHT is used to obtain a certain number of characters from the end (right) of a string.

Syntax

Example

Output

21. RTRIM

RTRIM is used to remove the spaces from the end of a string.

Syntax

Example

Output

22. SOUNDEX

SOUNDEX function returns soundex string of a given string. Soundex is an algorithm for indexing names after English pronunciation of sound.

All nonalphabetic characters in string are ignored, and all international alphabetic characters outside the A-Z range are treated as vowels.

This function works fine with strings that are in the English language. While in the case of other languages reliable results may or may not be produced.

This function does not guarantee to provide consistent results with strings that use multi-byte size character sets, including utf-8.

Syntax

Example

Output

23. SPACE

SPACE function is used for producing a string of spaces of a certain size as specified.

Syntax

Example

Output

24. STR

STR is used to obtain a value as a string.

Syntax

Example

Output

25. STUFF

STUFF function is used for replacing a substring with another given string.

Syntax

str1 is the string to be operated upon, M is the position in s1 to begin the operation from, N is the number of characters from m to remove, str2 is the string to be placed.

Example

Output

26. SUBSTRING

SUBSTRING is used to to obtain a substring from a given string.

Syntax

STR is the string to be operated upon, M is the position to start the operation from, N is the number of characters from M to be involved.

Example

Output

27. TRANSLATE

TRANSLATE function returns the string from the first argument string, after the characters specified in the second argument string are translated into the characters specified in the third argument string.

The TRANSLATE function returns an error if characters and translations have different lengths.

Syntax

Example

Output

28. TRIM

TRIM function is used for removing spaces from beginning and end of a string.

Syntax

Example

Output

29. UNICODE

UNICODE is used for obtaining the unicode integer value for the first character (leftmmost) character of a string.

Syntax

Example

Output

30. UPPER

UPPER function is used for coverting a string to uppercase letters.

Syntax

Example

Output

Function	Explanation
ASCII	Retrieves the ASCII value corresponding to a specific character.
CHAR	Produces the character based on the provided ASCII code.
CHARINDEX	Determines the position of a substring within a string.
CONCAT	Merges multiple strings together.
Concat with +	Concatenates multiple strings.
CONCAT_WS	Joins multiple strings using a specified separator.
DATALENGTH	Computes the byte length of an expression.
DIFFERENCE	Compares two SOUNDEX values and yields an integer representing their similarity.
FORMAT	Formats a value according to the specified format.
LEFT	Extracts a certain number of characters from the beginning of a string.
LEN	Computes the length of a string.
LOWER	Converts a string to lowercase.
LTRIM	Removes leading spaces from a string.
NCHAR	Provides the Unicode character corresponding to the given number code.
PATINDEX	Finds the position of a pattern within a string.
QUOTENAME	Generates a Unicode string with delimiters to ensure a valid SQL Server identifier.
REPLACE	Substitutes all occurrences of a substring within a string with a new substring.
REPLICATE	Duplicates a string a specified number of times.
REVERSE	Inverts a string's order.
RIGHT	Retrieves a certain number of characters from the end of a string.
RTRIM	Eliminates trailing spaces from a string.
SOUNDEX	Provides a four-character code for assessing the similarity between two strings.
SPACE	Generates a string consisting of a specified number of spaces.
STR	Converts a number into a string.
STUFF	Removes a section of a string and replaces it with another at a specified position.
SUBSTRING	Extracts a portion of a string.
TRANSLATE	Substitutes characters in the input string as specified by the mapping provided.
TRIM	It returns a new string by removing all the white spaces or other specified characters from the start, end, or both sides of the given String.
UNICODE	Retrieves the Unicode value for the first character of the input expression.
UPPER	Converts a string to upper-case.

Conclusion

Structured Query Language provides a set of data types and various operations associated with each of these data types. One of these data types is string.
There are many functions in SQL to operate upon string objects. Following are the prominent string functions in SQL: ASCII, CHAR, NCHAR,CHARINDEX, CONCAT, CONCAT_WS, operator +, DATALENGTH, DIFFERENCE, FORMAT, LEFT, RIGHT, LTRIM, RTRIM, TRIM, LEN, LENGTH, LOWER, UPPER, PATINDEX, QUOTENAME, REPLACE, REPLICATE, REVERSE, STR, SUBSTRING,SOUNDEX, SPACE, STUFF, TRANSLATE andUNICODE.