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.
|
1 2 |
SELECT SUBSTRING('Hi, You are on SQLSHACK.COM', 16, 12) result; |
We can understand the substring output using the following image.
If we change the starting position and length parameter, it returns the modified substring.
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.
|
1 2 3 4 5 |
Select firstname ,lastname,len(lastname) as LastNameLength FROM AdventureWorks2017.Person.Person WHERE SUBSTRING(FirstName, LEN(FirstName)-1,2) = 'el' |
Here, it gets the starting position dynamically depending upon the length of a person’s first name.
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 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.
|
1 2 3 |
SELECT Charindex('sqlshack.com', 'This is SQLSHACK.COM' COLLATE latin1_general_cs_as) AS Output; |
It returns zero in the output because it considers sqlshack.com separate from SQLSHACK.COM.
Let’s change the substring in the capital letter to match with the string.
|
1 2 3 |
SELECT <strong>Charindex</strong>('SQLSHACK.COM', 'This is SQLSHACK.COM' COLLATE latin1_general_cs_as) AS Output; |
It returns the position of SUBSTRING using the case-sensitive search using the CHARINDEX function.
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.
|
1 2 |
SELECT CHARINDEX('SQLSHACK', 'SQLSHACK - SQLSHACK.COM') SELECT CHARINDEX('SQLSHACK', 'SQLSHACK - SQLSHACK.COM',8) |
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.
|
1 2 |
SELECT <strong>Patindex</strong>('%author%', 'You are a prominient author at SQLShack') position; |
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.
|
1 2 |
SELECT <strong>Patindex</strong>('%SQ_Shack%', 'You are a prominent author at SQLShack') position; |
In the below SQL query, we use the [^] string operator. It finds out the position of the character without an alphabet, number or space.
|
1 2 |
SELECT position = PATINDEX('%[^ 0-9A-z]%', 'You are a prominent author at SQLShack!'); |
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.
|
1 2 3 4 5 6 7 8 9 |
SELECT name, PATINDEX('%Frame%', name) position FROM production.product WHERE name LIKE '%Yellow%' ORDER BY name; |
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.
|
1 2 3 4 5 |
SELECT a.emailaddress, Substring (a.emailaddress, Charindex( '@', emailaddress ) + 1, Len(emailaddress)) AS [Domain Name] FROM [AdventureWorks2017].[Person].[EmailAddress] a |
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
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.
|
1 2 3 4 5 |
Select [Messages], substring ([Messages], charindex('/',[Messages])-3,charindex(',',[Messages])-charindex('/',[Messages])-5) as Date from [ImportantDates] |
In the output, we get the dates from the [Messages] column strings.
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am 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 my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023