Hadi Fadlallah

Yet another bunch of SQL string functions

January 13, 2020 by

This article is a supplement to the previously published article, An overview of SQL String Functions . This time, we will describe another bunch of SQL string functions used in SQL Server.

PATINDEX

PATNDEX() is a scalar SQL string function that returns the index of a specific string pattern within a given string. PATINDEX() mainly takes 2 parameters, which are the input string and the pattern. Also, it has one optional parameter which is the starting index of the search operation (default value is zero which means that the search starts from the beginning of the input string).

The function return type depends on the input string length; if it is NVARCHAR(MAX), then it will return a BIGINT value else it will return an INT value.

In order to learn more about writing patterns in SQL Server, you can refer to the LIKE (Transact-SQL) documentation.

Example:

Result:

6

Screenshot:

Example of PATINDEX sql string function

In the example above, we search for a number followed by M character within the PC-105M5MNC input string, and the index returned is 6.

QUOTENAME

QUOTENAME() is a scalar SQL string function used to delimit an input string using a given quote character. The function has one required parameter, which is the input string and an optional parameter which is the delimiter. The default quote characters are brackets []. Note that only a few characters are accepted, or the function will return NULL, as shown in the example below. Quote characters can be brackets [], single quotes ‘’, double quotes “”, parenthesis (), braces {}, greater than and less than signs <>.

Example:

Result:

{abc} NULL

Screenshot:

Example of QUOTENAME sql string function

REPLICATE

REPLICATE() is a scalar function used to repeat an input string and repeat it for a specified number of times. Both parameters are required to execute this function.

Example:

Result:

100100100100100

Screenshot:

example of REPLICATE SQL string function

REVERSE

REVERSE() is a scalar SQL string function used to reverse an input string.

Example:

Result:

dlroW olleH

Screenshot:

example of REVERSE string function

REPLACE

REPLACE() is a string function used to replace all occurrences of a specific string with another string. This function takes three parameters: (1) input string, (2) string to be replaced and (3) string to replace with.

Example:

Result:

This is my World

Screenshot:

example of REPLACE string function

In the example above, we converted Hello World string to This is my World using REPLACE() function.

SPACE

SPACE() is a SQL string function used to add a specified number of spaces.

Example:

Result:

Hello World

Screenshot:

Example of SPACE string function

STRING_AGG

STRING_AGG() is a SQL string function used to concatenate the values of a column separated by a specified delimiter. This function takes two parameters: (1) the string expression to be concatenated, (2) the delimiter. One of the most use cases for this function is to generate comma-separated values using a specific expression. There is more information related to the syntax of this function and several examples that can be found in the official documentation.

Example:

Result:

Mark Zuckerberg,Donald Trump

Screenshot:

Example of STRING_AGG() sql string function

STRING_ESCAPE

STRING_ESCAPE() is a string function introduced in SQL Server 2016. It is used to add escape characters before all special characters found within a string. This function takes two parameters: (1) the input string and (2) the escaping rules that will be applied. Until now, only one ‘json’ escaping rule is supported. This function is mainly used to generate JSON strings or when the generated string will be used by another application where special characters must be preceded by an escape character.

Example:

Result:

\\\t\” \/

Screenshot:

example of STRING_ESCAPE() string function

STRING_SPLIT

A table-valued function introduced in SQL Server 2016. It splits a string into rows of substrings, based on a specified separator character.

If you used a previous version of SQL Server, you could create your own table-valued function to split a string based on a specific delimiter. You can refer to the following topics on www.stackoverflow.com for many examples:

Example:

Result:

Mark

Donald

Peter

Screenshot:

example of STRING_SPLIT() string function

STUFF

STUFF() is a scalar function used to insert a string within another string at a specified index after deleting a specified set of characters. This function takes four parameters: (1) input string, (2) index where the string insertion must be done, (3) length of characters to be deleted starting the index specified, (4) string to be inserted.

One of the most popular use cases of this function is to remove additional separator at the beginning of a concatenated string, as shown in the example below.

Example:

Result:

,Mark Zuckerberg,Donald Trump Mark Zuckerberg,Donald Trump

Screenshot:

Example of STUFF() string function

In the example above, we can see that concatenating the values of a column using the old approach may result in an additional separator at the beginning of the result. This additional separator can be easily removed using the STUFF() function.

SUBSTRING

SUBSTRING() is the last scalar SQL string function we will describe in this overview. It is used to extract a specific number of characters from a given string. This function takes three parameters: (1) input string, (2) index where extraction will start and (3) length of the number of characters be extracted. Note that if the number of characters is bigger than the length of the input string, it will only extract available characters.

Example:

Result:

Hello World

Screenshot:

Example of SUBSTRING() sql string function

Conclusion

In this article, we gave an overview of another group of built-in SQL string functions in SQL Server that we started in the previous article, we also provided some examples and screenshots and we discussed the results obtained.

Hadi Fadlallah

Hadi Fadlallah

Hadi is a Lebanese Researcher, Data Engineer and Business Intelligence Developer.

He has been working with SQL Server for more than 10 years. Also, he's one of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com

Hadi really enjoys learning new things everyday and sharing his knowledge.

View all posts by Hadi Fadlallah
Hadi Fadlallah

Latest posts by Hadi Fadlallah (see all)

98 Views