SQL String Functions

Introduction

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:

Four rows should be enough for all of our testing purposes. You can definitely as many rows as you wish for.

SQL String Functions

String manipulation is a common and important operation in programming. Maybe we need to obtain a substring from a given string. Another use case could be when the user needs to convert all the characters of a given string to upper case / lower case. SQL provides us with a host of useful functions for performing many different types of operations on strings.

ASCII

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

Syntax

Example

Output

CHAR

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

Syntax

Here n1, n2, ... are the numbers passed as parameters to the char() call. Example

Output

CHARINDEX

CHARINDEX returns the position of a character in a string.

Syntax

CH is a character and str is a string object. Example

Output

CONCAT

CONCAT is used to concatenate (join) 2 expressions.

Syntax

exp1, exp2, ... are expressions to be concatenated.

Output

Example

Output

CONCAT with +

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

Syntax

exp1, exp2, ... are strings that you wish to concatenate.

Example

Output

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

DATALENGTH

DATALENGTH is used to obtain the length of a string.

Syntax

EXP is a string expression. Example

Output

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

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

LEFT

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

Syntax

Example

Output

LEN

LEN is used to obtain the length of a string.

Syntax

str is the string of which the length is to be found.

Example

Output

LOWER

LOWER is used to convert a string to lower case.

Syntax

STR is the string to be converted to lower case.

Example

Output

LTRIM

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

Syntax

STR is the string arg.

Example

Output

NCHAR

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

Syntax

N is the number code.

Example

Output

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

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:

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

REPLICATE

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

Syntax

s is the string to be replicated and n is the number of times the string should be replicated.

Example

Output

REVERSE

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

Syntax

str is the string to be reversed.

Example

Output

RIGHT

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

Syntax

str is the string argument to be operated upon.

Example

Output

RTRIM

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

Syntax

str is the string argument to be operated upon.

Example

Output

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

STR is the string parameter to be operated upon.

Example

Output

SPACE

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

Syntax

N is the size of the string required.

Example

Output

STR

STR is used to obtain a value as a string.

Syntax

val is the argument value to be cast as string.

Example

Output

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

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

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

TRIM

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

Syntax

STR is the argument string.

Example

Output

UNICODE

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

Syntax

Example

Output

UPPER

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

Syntax

Example

Output