SQL Server provides a wide range of string functions that can be used to manipulate and analyze text data. In this article, we will explore some of the most commonly used string functions in SQL Server.

PATINDEX

The PATINDEX function is used to find the index of a specific string pattern within a given string. It takes two parameters: the input string and the pattern to search for. Additionally, you can specify an optional starting index for the search operation. The function returns the index of the first occurrence of the pattern in the input string.

Example:

SELECT PATINDEX('%[0-9]M%', 'PC-105M5MNC') AS Result;

Result: 6

QUOTENAME

The QUOTENAME function is used to delimit an input string using a specified quote character. It takes the input string as a required parameter and an optional delimiter parameter. The default quote characters are brackets [].

Example:

SELECT QUOTENAME('abc', '{') AS Result;

Result: {abc}

REPLICATE

The REPLICATE function is used to repeat an input string for a specified number of times. It takes two parameters: the input string and the number of times to repeat it.

Example:

SELECT REPLICATE('100', 5) AS Result;

Result: 100100100100100

REVERSE

The REVERSE function is used to reverse an input string.

Example:

SELECT REVERSE('Hello World') AS Result;

Result: dlroW olleH

REPLACE

The REPLACE function is used to replace all occurrences of a specific string with another string. It takes three parameters: the input string, the string to be replaced, and the string to replace with.

Example:

SELECT REPLACE('Hello World', 'Hello', 'This is my') AS Result;

Result: This is my World

SPACE

The SPACE function is used to add a specified number of spaces.

Example:

SELECT 'Hello' + SPACE(3) + 'World' AS Result;

Result: Hello World

STRING_AGG

The STRING_AGG function is used to concatenate the values of a column separated by a specified delimiter. It takes two parameters: the string expression to be concatenated and the delimiter.

Example:

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

STRING_ESCAPE

The STRING_ESCAPE function is used to add escape characters before special characters found within a string. It takes two parameters: the input string and the escaping rules to be applied.

Example:

SELECT STRING_ESCAPE('\"/', 'json') AS Result;

Result: \\\t\” \/

STRING_SPLIT

The STRING_SPLIT function is used to split a string into rows of substrings based on a specified separator character. It was introduced in SQL Server 2016.

Example:

SELECT value FROM STRING_SPLIT('Mark,Donald,Peter', ',');

Result: Mark Donald Peter

STUFF

The STUFF function is used to insert a string within another string at a specified index after deleting a specified set of characters. It takes four parameters: the input string, the index where the insertion should be done, the length of characters to be deleted, and the string to be inserted.

Example:

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, '') AS Result;

Result: ,Mark Zuckerberg,Donald Trump Mark Zuckerberg,Donald Trump

SUBSTRING

The SUBSTRING function is used to extract a specific number of characters from a given string. It takes three parameters: the input string, the index where extraction should start, and the length of the number of characters to be extracted.

Example:

SELECT SUBSTRING('Hello World', 1, 5), SUBSTRING('Hello World', 6, 10) AS Result;

Result: Hello World

In this article, we have explored various SQL Server string functions that can be used to manipulate and analyze text data. These functions provide powerful tools for working with strings in SQL Server queries.