Sql Server: Query to get string between two characters or symbols

Introduction: In this article I am going to share the query to extract substring from within two special characters or symbols in sql server. Or we can say getting text from string wrapped between two same or different characters.

In previous articles i explained How to Drop or truncate parent table by dropping all foreign key constraints and Query to search any text in all stored procedures, views and functions and Remove first or last character from string or column in sql server and Convert or split comma separated string into table rows in sql server and Temporary tables, their types and examples to use

Description: While working with sql server database I got the requirement to get substring from within two specific characters or symbols. To get this done we just need to find the first and last index of specified character and then using substring function we can extract the desired part as demonstrated in example below.

Implementation: Let’s create an example to see it in action.

--Create a temporary table using following script

CREATE TABLE #tb

(

            Code VARCHAR(25)

)

--Insert some dummy data into table

INSERT INTO #tb VALUES

('BRF/145/23'), ('ZRF/846/63'), ('ABC/123/79')

--Show dummy data

SELECT * FROM #tb 

Code

BRF/145/23

ZRF/846/63

ABC/123/79

Suppose it is required to extract the text written between '/'. Then the query will be as:

--Query to extract the desired code

SELECT SUBSTRING(Code,CHARINDEX('/',Code)+1,(((LEN(Code))-CHARINDEX('/', REVERSE(Code)))-CHARINDEX('/',Code))) AS Result FROM #tb 

Result

145

846

123

Note: If you want to extract data between different characters  e.g. if your data is like 'BRF/145#23' then the query will be bit modified as:

SELECT SUBSTRING(Code,CHARINDEX('/',Code)+1,(((LEN(Code))-CHARINDEX('#', REVERSE(Code)))-CHARINDEX('/',Code))) AS Result FROM #tb

  Now over to you:

A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates. 

• Tweet
• Share
• Share
• Share

Related Post