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

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

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

Example 2: Use SUBSTRING within T-SQL Query

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

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

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

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

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

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

PATINDEX ( '%StringPattern%' , input_string )

Example 1: Use PATINDEX in a literal string input

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

Example 2: Use of PATINDEX with wildcards

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

Example 3: Use PATINDEX within T-SQL Query

--

--

--

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

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Prepare for Google Cloud Certification with hands-on learning!

Kubernetes Tip: Is CI/CD Really Popular?.

InfinityDB — A NoSQL Multi-Value Database you most know!

The Functor class in Haskell, what is it?

Basic Linux command line tutorial to start developing in Ubuntu Linux

Error: AmazonS3Exception “Access Denied with Status Code: 403” in Amazon Athena when I query a…

TryHackMe | UltraTech

System Design Interview Advice

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/

More from Medium

Stored Procedures in MySQL

5 Advance SQL functions you must know

Somewhat complex reporting and data analysis in MySql 8 — Part 1

Starting SQL: Intro to JOINs