Introduction:

When working with string data in SQL Server, we often need to perform various calculations, analytics, and search operations. SQL Server provides several useful string functions such as SUBSTRING, PATINDEX, and CHARINDEX to help us achieve these tasks. In this article, we will explore these functions with examples.

SUBSTRING function in SQL queries

The SUBSTRING() function allows us to extract a substring from a specified string based on a given starting position and length.

Syntax for SUBSTRING() function:

SUBSTRING(expression, starting_position, length)

Expression: This argument specifies the character, binary, text, ntext, or image expression from which we want to extract the substring.

Starting_position: It is an integer or bigint expression that defines the starting position from where we want to extract the substring. The first character in the string has a value of 1.

Length: It is a positive integer value that defines the number of characters we want to retrieve from the string, starting from the starting_position.

Example:

SELECT SUBSTRING('Hi, You are on SQLSHACK.COM', 16, 12) AS result;

In the above example, we retrieve a substring using the specified inputs. The result will be “SQLSHACK.COM”.

We can also use the SUBSTRING function with an expression. In the following example, we calculate the length of the [lastname] column using the LEN() function and use it as the starting_position argument:

SELECT firstname, lastname, LEN(lastname) AS LastNameLength
FROM AdventureWorks2017.Person.Person
WHERE SUBSTRING(FirstName, LEN(FirstName) - 1, 2) = 'el';

This query dynamically determines the starting position based on the length of a person’s first name.

CHARINDEX function in SQL queries

The CHARINDEX() function returns the position of a substring within a specified string. It works in the opposite way to the SUBSTRING function.

Syntax of CHARINDEX() function:

CHARINDEX(substring, input_string)

Substring: This argument specifies the substring that we want to search for in the input string. We can specify a maximum of 8000 characters in this argument.

Input_String: This argument specifies the input string.

Example:

SELECT CHARINDEX('SQLSHACK.COM', 'This is SQLSHACK.COM') AS Output;

In the above example, we retrieve the starting position of the substring “SQLSHACK.COM” using the CHARINDEX function. The output will be 16.

The CHARINDEX function can also perform case-sensitive searches. To do this, we need to use the COLLATE() function with a case-sensitive collation. For example:

SELECT CHARINDEX('SQLSHACK.COM', 'This is SQLSHACK.COM' COLLATE latin1_general_cs_as) AS Output;

In the above query, we use the COLLATE function along with the collation “latin1_general_cs_as” to perform a case-sensitive search. The output will be the position of the substring “SQLSHACK.COM”.

We can also specify an optional starting position in the CHARINDEX() function. For example:

SELECT CHARINDEX('SQLSHACK', 'SQLSHACK - SQLSHACK.COM') AS Output;
SELECT CHARINDEX('SQLSHACK', 'SQLSHACK - SQLSHACK.COM', 8) AS Output;

In the above example, the second query specifies a starting position of 8. Therefore, it starts looking for the substring from the 8th character position.

PATINDEX function in SQL queries

The PATINDEX() function searches for the first occurrence of a pattern in the input string and returns its starting position.

Syntax of PATINDEX() function:

PATINDEX(‘%Pattern%’, input_string)

Pattern: This argument specifies the character expression that we want to search for in the input string. We can include wild-characters in this argument.

Input_String: This argument specifies the string in which we want to search for the pattern.

Example:

SELECT PATINDEX('%author%', 'You are a prominent author at SQLShack') AS position;

In the above example, we search for the pattern “%author%” in the specified string. The output will be the starting position of the pattern.

We can also use the wildcard characters “%” and “_” to find the positions of patterns. For example:

SELECT PATINDEX('%SQ_Shack%', 'You are a prominent author at SQLShack') AS position;

In the above example, we search for the pattern “SQ_Shack” in the string. It is similar to using the LIKE operator.

We can also use the PATINDEX() function to find the position of a character that is not an alphabet, number, or space. For example:

SELECT position = PATINDEX('%[^0-9A-z]%', 'You are a prominent author at SQLShack!');

In the above example, we use the PATINDEX() function to find the position of a character that is not an alphabet, number, or space.

We can also use the PATINDEX() function with table columns. For example:

SELECT name, PATINDEX('%Frame%', name) AS position
FROM production.product
WHERE name LIKE '%Yellow%'
ORDER BY name;

In the above example, we use the PATINDEX() function to check for the pattern “Frame” in the [Name] column of the [Production].[Product] table.

Use of SUBSTRING and CHARINDEX functions together in SQL queries

In many cases, we combine the SUBSTRING and CHARINDEX functions to achieve the desired result. For example, suppose we have a table that stores email addresses for customers, and we want to fetch the domain names (e.g., gmail.com, outlook.com) from the email addresses. We can use the following query:

SELECT a.emailaddress, SUBSTRING(a.emailaddress, CHARINDEX('@', emailaddress) + 1, LEN(emailaddress)) AS [Domain Name]
FROM AdventureWorks2017.Person.EmailAddress a;

In the above query, we combine the CHARINDEX and SUBSTRING functions to extract the domain names from the email addresses.

Similarly, suppose we have a table that stores employee joining dates, times, and days, and we want to extract the joining dates in a separate column. We can use the following query:

SELECT [Messages], SUBSTRING([Messages], CHARINDEX('/', [Messages]) - 3, CHARINDEX(',', [Messages]) - CHARINDEX('/', [Messages]) - 5) AS Date
FROM [ImportantDates];

In the above example, we use the CHARINDEX and SUBSTRING functions to extract the joining dates from the [Messages] column strings.

Conclusion

In this article, we explored the SUBSTRING, PATINDEX, and CHARINDEX string functions in SQL Server. These functions are useful for retrieving specific text or data from strings. As a beginner, you can start by using these functions individually and later combine them to achieve more complex tasks.