Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Extracting a String Within Delimeters - Part 2

By Stephen Lasham,

Extracting a string from between two delimiting characters (Part 2)

I recently received correspondence from Denis Oliynik, IT manager at JSC 'Rise', Kiev in the Ukraine, who kindly provided me with a script improving on my previously published solution.  Denis's solution removes the CASE statement for handling where a second delimiting character is not present.  This simplified code explained below offers a clever trick to add to your SQL skills.

Sample data

Here is the DML and DDL to create a sample table and insert some data

CREATE TABLE Sample_table (Particulars CHAR(120))
GO
INSERT INTO Sample_table VALUES('LDR ; LUC20031026901 ; Vehicle')
INSERT INTO Sample_table VALUES('LDR ; Consolidated')
INSERT INTO Sample_table VALUES('LDR ; SUB35030172701 ; Building')
INSERT INTO Sample_table VALUES('LDRR ; LIQ200310121 ; Liquor')

Solution

SELECT Particulars,
     LTRIM(RTRIM(SUBSTRING(
       /* <text>> */ Particulars,
       /* <start > */   CHARINDEX(';',Particulars,1) + 1,
       /* <length> */   CHARINDEX(';', 
          SUBSTRING(Particulars, CHARINDEX(';', Particulars, 1) + 1, 99) + ';') - 1 
      ) ) ) AS Result_string
FROM Sample_table

How it works

The problem previously was to identify the length from the first semi-colon to the second semi-colon.  If the second semi-colon was absent there was no reference point to work with and unless tested for by a prior CASE statement, the solution failed.  This solution overcomes the problem by adding in a semi-colon to the end of the string extracted by the SUBSTRING function.

In our examples the results of the above SUBSTRING line will look as follows

LUC20031026901 ; Vehicle;
Consolidated;
SUB35030172701 ; Building;
LIQ200310121 ; Liquor;

The original string for "Consolidated" did not have a semi-colon, which has now been added. This will be recognised by the second CHARINDEX to determine the end position.  The other three already had their second semi-colon, which the CHARINDEX will recognise.  In these instances the added semi-colon is not referenced.  Subtracting 1 from the result gives us the length less the semi-colon, and the resulting sting can be extracted.

Acknowledgment

Many thanks to Denis Oliynik, for showing that for every solution there is often a better one, and proving we are truly a global community. 

I hope the solution proves useful and feedback is welcomed.

Total article views: 7821 | Views in the last 30 days: 1
 
Related Articles
FORUM

Searching for URL contents using CHARINDEX

Providing CHARINDEX parameter from table

FORUM

Problem segregating third occurence from a column

Charindex ,substring

FORUM

code sample

code sample

BLOG

String Manipulation - CHARINDEX()

I see charindex used quite commonly in string manipulation.  What I rarely see used is the optional ...

BLOG

Slides & Samples for my SQLPASS sessions

As announced in both of my session you can find my slides & samples here for download: Building F...

Tags
advanced querying    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones