Rajendra Gupta
Use of SUBSTRING and CHARINDEX functions together

SUBSTRING, PATINDEX and CHARINDEX string functions in SQL queries

March 1, 2021 by

In this article, we will explore SUBSTRING, PATINDEX and CHARINDEX string functions for SQL queries.

Introduction

While working with the string data, we perform various calculations, analytics, search, replace strings using SQL queries. SQL Server provides many useful functions such as ASCII, CHAR, CHARINDEX, CONCAT, CONCAT_WS, REPLACE, STRING_AGG, UNICODE, UPPER for this purpose. In this article, we explore SUBSTRING, PATINDEX, and CHARINDEX using examples.

SUBSTRING function in SQL queries

The SUBSTRING() function extracts the substring from the specified string based on the specified location.

Syntax for SUBSTRING() function:

SUBSTRING(expression, starting_position, length)

  • Expression: In this argument, we specify a character, binary, text, ntext, or image expression
  • starting_position: It contains an integer or bigint expression. It defines the starting position from where we extract the substring. The first character in the string starts with the value 1
  • Length: It is a positive integer value that defines how many characters from the string, from the starting_position, we want to retrieve

In the below example, we retrieve a substring using the specified inputs.

SUBSTRING function example

We can understand the substring output using the following image.

Understand SUBSTRING function output

If we change the starting position and length parameter, it returns the modified substring.

position and length parameter

SUBSTRING function with an expression

In the below SQL query, we use the LEN() function to calculate the [lastname] length in the starting_position argument.

Here, it gets the starting position dynamically depending upon the length of a person’s first name.

SUBSTRING function with an expression

CHARINDEX function in SQL queries

The CHARINDEX() function returns the substring position inside the specified string. It works reverse to the SUBSTRING function. The substring() returns the string from the starting position however the CHARINDEX returns the substring position.

Syntax of CHARINDEX() function:

CHARINDEX(substring, input_string)

  • Substring: Here, we define the substring that we want to search in the input string. We can specify a maximum of 8000 characters in this argument
  • Input_String: In this argument, we define the input string

In the below example, we retrieve the position of substring SQLSHACK.COM using the CHARINDEX. It returns the starting position of the substring as 16. In the earlier example of the SUBSTRING function, we specified the starting position 16 to returns the SQLSHACK.COM string.

CHARINDEX function

CHARINDEX function can also perform case-sensitive searches. We need to use the COLLATE() function for a case-sensitive search.

For example, in the below query, we use the COLLATE function along with the collation latin_general_cs_as.

It returns zero in the output because it considers sqlshack.com separate from SQLSHACK.COM.

COLLATE() function

Let’s change the substring in the capital letter to match with the string.

It returns the position of SUBSTRING using the case-sensitive search using the CHARINDEX function.

substring in the capital letter

We can also add an optional starting position in the CHARINDEX() function. For example, in the below query, the 2nd query specifies a starting position at 8. Therefore, it starts looking for the substring from the 8th character position.

starting position in the CHARINDEX() function

PATINDEX function in SQL queries

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

Syntax of PATINDEX() function:

PATINDEX ( ‘%Pattern%’,input_string )

  • %Pattern%: In this argument, we specify the character expression that we want to look into the specified string. We might include the wild-characters as well in this argument
  • input_string: It is the string in which we want to search the pattern

In the below example, we search the pattern %author% in the specified string.

PATINDEX function

You can use the wildcard character % and _ to find the positions of the pattern as well. In this example, we search the position for the pattern SQ followed by Shack in the string. It is similar to a LIKE operator.

wildcard character %

In the below SQL query, we use the [^] string operator. It finds out the position of the character without an alphabet, number or space.

string operator

In the below example, we use the PATINDEX() function for a table column. It checks for the pattern – frame in the [Name] column of the [Production].[Product] table.

PATINDEX() function for a table column

Use of SUBSTRING and CHARINDEX functions together in SQL queries

In many cases, we combine these functions to produce the required result. For example, Suppose your table holds mail addresses for your customer. You want to fetch the domain names ( such as gmail.com, outlook.com) from the email addresses.

In the below query, we combine the CHARINDEX and SUBSTRING function.

You can understand the output of the query using the below image.

  • The CHARINDEX function returns the position of character @ from the [emailaddress] column
  • The LEN() function is for calculating the length of the email address
  • The SUBSTRING function returns the substring from the [emailaddress] as per the position returned by the CHARINDEX

Use of SUBSTRING and CHARINDEX functions together

Similarly, suppose you have a table that employee joining date, time and day. You want to extract the joining date in another column. Similar to the other example, you can use the CHARINDEX, and SUBSTRING() function in the below SQL query.

In the output, we get the dates from the [Messages] column strings.

Extract required data

Conclusion

In this article, we explored the SUBSTRING, PATINDEX, and CHARINDEX string functions for SQL queries. You can retrieve a specific text, data using a combination of these functions. As a beginner, you can first write these functions individually and later combine the functions.

Rajendra Gupta
SQL commands, String functions, T-SQL

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

2,032 Views