T-SQL Regular Expressions: SUBSTRING, PATINDEX, and CHARINDEX

SUBSTRING

SQL Server SUBSTRING() function is used to extract the substring from the given input_string. It extracts the substring, starting from the specified position defined by the parameter.
Following is the syntax for the SUBSTRING()

SUBSTRING(input_string, starting_position, length)
  1. Input_string. This parameter defines a string expression from which you want to extract the substring. This parameter can be text, character, or binary string.
  2. Starting_position. This parameter defines a starting position from where you want to extract the substring. This parameter must be an integer value. You can use only one starting position parameter.
  3. Length. This parameter defines a position of the character you want to extract from the input string. This parameter must be an integer value.

Example 1: Use SUBSTRING using literal string input

For example, I want to extract the word “DBA” from the input string “Nisarg is DBA.” In an input string, the position of the character D is eleven, and we want to extract three characters, therefore, the value of the “starting_position” parameter is 11, and the value of the “length” parameter is 3.

SELECT Substring('Nisarg is DBA', 11, 3)
Substring
---------
DBA

Example 2: Use SUBSTRING within T-SQL Query

I have created a sample table named “IPDREGISTRATION” in the VSData database. In the table, there is a column named “IPDREGNO,” which represents patients’ registration number. The IPDREGNO is a unique number and its format is <IP> <Financial_Year><6 digit IncrementalNumber>. For example, IP1920000001.

USE vsdata 
go
SELECT Substring(ipdregno, 7, 6)AS IPDNO,
patientname
FROM vsdata.ipdregistration
IPDNO  	PatientName
------ ---------------------------------
000002 PARIMAL GANDHI
000001 RAHEMAN KASIM
000003 JANVI BHANUBHAI
000006 HEENABEN RAMESHBHAI
000004 MOHMAD SAFAN MOHMAD HANIF
000005 PREMGIBHAI VADHABHAI
000007 ASHIS SURYAKANT
000008 KULSUMBAI RAHIMBHAI
000009 BABY
000010 RANCHODEBHAI BANABHAI<\pre>

Example 3: Extract the domain name from the email address

For example, I want to extract the domain name from the email address field of any table. To achieve that, we will use the substring and CHARINDEX() function. The following script can be used to extract the domain name from the email address.

SELECT a.firstname, 
a.lastname,
a.emailaddress,
Substring (a.emailaddress, Charindex( '@', emailaddress ) + 1, -- Start Position
Len(emailaddress) -- End Position.
) AS [Domain Name]
FROM users a
SELECT Count(1), 
Substring (a.emailaddress, Charindex( '@', emailaddress ) + 1, Len(
emailaddress)
) AS [Domain Name]
FROM users a
GROUP BY Substring (a.emailaddress, Charindex( '@', a.emailaddress ) + 1,
Len(a.emailaddress))

CHARINDEX

SQL Server CHARINDEX() function is used to search the position of a substring inside an input string. Unlike SUBSTRING(), this function starts the search from a specified location and returns the position of the substring. If a substring is not found, it returns zero. CHARINDEX() function is used to perform case sensitive and case insensitive searches based on the collation specified in the query.
Following is the syntax of the CHARDINDEX() function.

CHARINDEX(substring, input_string [, start_location])
  1. Substring. This argument defines the substring that you want to search within the input string.
  2. Input_string. This argument defines the input string.
  3. Start_location. This argument defines the location from which you want to start the search within the input string. The data type of this parameter is an integer, and this is an optional parameter. If this parameter is not specified, then the search starts from the beginning of the input string.

Example 1: Use CHARINDEX using literal string input

For example, I want to check the position of the “example” substring from the input string “This is CHARINDEX example”. To do that, execute the following query:

SELECT Charindex('example', 'This is CHARINDEX example')AS Output
Output
-----------
19
SELECT Charindex('examples', 'This is CHARINDEX example')AS Output
Output
-----------
0

Example 2: Perform case-sensitive search

As I mentioned above, we can perform a case-sensitive search using the CHARINDEX function. To do that we have to use COLLATE function within a query, For example, I want to get the position of “CHARINDEX” substring from the input string “This is CHARINDEX example” To do that execute the following query:

SELECT Charindex('charindex', 'This is CHARINDEX example' COLLATE 
latin1_general_cs_as)
AS Output
Output
-----------
0
SELECT Charindex('charindex', 'This is CHARINDEX example')AS Output
Output
-----------
9

Example 3: Get the database file name using the T-SQL function

For example, I want to populate the database and log file name of all databases. To get the file names, we are going to use a dynamic management view named sys.master_files. Execute the following query to create a function.

CREATE FUNCTION [DBO].[GETFILENAME] (@PATH NVARCHAR(MAX)) 
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @FILENAME NVARCHAR(MAX)
DECLARE @REVERSEDPATH NVARCHAR(MAX)
SET @REVERSEDPATH = REVERSE(@PATH)
SELECT @FILENAME = RIGHT(@PATH, CHARINDEX('\', @REVERSEDPATH) - 1)
RETURN @FILENAME
END
USE vsdata
go
SELECT Db_name(database_id) AS DatabaseName,
physical_name AS [Database File Location],
(SELECT dbo.Getfilename(physical_name))AS[Database File Name]
FROM sys.master_files
WHERE database_id > 5

PATINDEX

The SQL Server PATINDEX() function returns the position of a pattern within an input string. Following is the syntax of PATINDEX():

PATINDEX ( '%StringPattern%' , input_string )

Example 1: Use PATINDEX in a literal string input

Let’s consider a simple example of PATINDEX. For example, I want to find the position of the word “example” in the “Wild-card example” input string. To do that, execute the following query:

SELECT Patindex('%example%', 'Wild-card example') position;
position
-----------
11

Example 2: Use of PATINDEX with wildcards

We can use wild-card characters to find the position of a specific word within an input string. In the following example, we use % and _ wild-card to find the position of a word in which the first two characters are “Wi”, followed by any other two characters and -card in input string “PATINDEX Wild-card example”.

SELECT Patindex('%Wi__-card%', 'PATINDEX Wild-card example') position;
position
-----------
10

Example 3: Use PATINDEX within T-SQL Query

For example, we want to retrieve the position of the first occurrence of the pattern “wideworldimporters.com.” In the following query, I use the PATINEDX() function on the Logon column of the Application.people table of the WideWorldImportors database. You can download the sample database from the internet.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
{coding}Sight

{coding}Sight

Awesome blog focused on databases and Microsoft, .NET and cloud technologies. http://codingsight.com/