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:
|
1 |
select PATINDEX('%[0-9]M%','PC-105M5MNC') |
Result:
6
Screenshot:
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:
|
1 |
SELECT QUOTENAME('abc','{'), QUOTENAME('abc',',') |
Result:
{abc} NULL
Screenshot:
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:
|
1 |
SELECT REPLICATE('100',5) |
Result:
100100100100100
Screenshot:
REVERSE
REVERSE() is a scalar SQL string function used to reverse an input string.
Example:
|
1 |
SELECT REVERSE('Hello World') |
Result:
dlroW olleH
Screenshot:
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:
|
1 |
SELECT REPLACE('Hello World','Hello','This is my') |
Result:
This is my World
Screenshot:
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:
|
1 |
SELECT 'Hello' + SPACE(3) + 'World' |
Result:
Hello World
Screenshot:
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:
|
1 2 3 4 5 6 |
CREATE TABLE #TEMP(FirstName varchar(50), LastName varchar(50)) INSERT INTO #TEMP(FirstName,LastName) VALUES ('Mark', ' Zuckerberg'), ('Donald', 'Trump') SELECT STRING_AGG(FirstName + ' ' + LastName, ',') FROM #TEMP |
Result:
Mark Zuckerberg,Donald Trump
Screenshot:
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:
|
1 |
SELECT STRING_ESCAPE('\ " /','json') |
Result:
\\\t\” \/
Screenshot:
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:
|
1 |
SELECT value FROM STRING_SPLIT('Mark,Donald,Peter',',') |
Result:
Mark
Donald
Peter
Screenshot:
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:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE #TEMP(FirstName varchar(50), LastName varchar(50)) INSERT INTO #TEMP(FirstName,LastName) VALUES ('Mark', ' Zuckerberg'), ('Donald', 'Trump') DECLARE @str varchar(500) = '' SELECT @str = @str + ',' + FirstName + ' ' + LastName FROM #TEMP SELECT @str, STUFF(@str,1,1,'') |
Result:
,Mark Zuckerberg,Donald Trump Mark Zuckerberg,Donald Trump
Screenshot:
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:
|
1 |
SELECT SUBSTRING('Hello World',1,5), SUBSTRING('Hello World',6,10) |
Result:
Hello World
Screenshot:
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.
Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional.
On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques.
Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website.
View all posts by Hadi Fadlallah
- An overview of SQL Server monitoring tools - December 12, 2023
- Different methods for monitoring MongoDB databases - June 14, 2023
- Learn SQL: Insert multiple rows commands - March 6, 2023