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

Extracting a String From Between Two Delimiting Characters

By Stephen Lasham,

Extracting a string from between two delimiting characters

The problem

A table contains a column (particulars) consisting of a semi-colon-delimited string of text, for which the text between the first and second semi-colon needs to be extracted.  The extracted text will be no longer than 99 characters.

The position of the first and second semi-colons is variable and the second semi-colon may not be present.

Extract the text between the semi-colons.

Sample data

Table : Sample_table

Column : Particulars

LDR ; LUC20031026901 ; Vehicle

LDR ; Consolidated

LDR ; SUB35030172701 ; Building

LDRR ; LIQ200310121 ; Liquor

Solution

SELECT Particulars,
   CASE
	-- when no second semi-colon,
	WHEN CHARINDEX(';',(SUBSTRING(Particulars,CHARINDEX(';',Particulars,1)+1,99))) = 0
	-- then extract the full string from first semi-colon to the max length of 99
		THEN LTRIM(RTRIM(SUBSTRING(Particulars,CHARINDEX(';',Particulars,1)+1,99)))
	-- else extract the full string from the first semi-colon up to second semi-colon
	ELSE LTRIM(RTRIM(SUSTRING(
       /* <text>> */	particulars, 
       /* <start > */	CHARINDEX(';',Particulars,1) + 1,
       /* <length> */	CHARINDEX(';', SUBSTRING(Particulars, 
		       			CHARINDEX(';', Particulars, 1) + 1, 
				     	99)) - 1 
        ) ) )
	END    AS Result_string
 FROM Sample_table

Resulting data

Particulars

Result_string

LDR ; LUC20031026901 ; Vehicle

LUC20031026901

LDR ; Consolidated

Consolidated

LDR ; SUB35030172701 ; Building

SUB35030172701

LDR ; LIQ200310121 ; Liquor

LIQ200310121

Method in Detail

SUBSTRING is used to extract the actual string value required.

	SUBSTRING(<text>, <start>, <length>)

	<text> = Particulars

CHARINDEX is used to identify the position of the semi-colons within the string.
CHARINDEX(, , )

The start position is easy, being the position of the first semi-colon plus 1 character.

	 = CHARINDEX(';', Particulars, 1) + 1

The string length is the length of the text from <Start_pos> to the second semi-colon. This requires a second SUBSTRING to get a revised string of all text following the first semi-colon. A CHARINDEX over this revised text (less one character) gives the length.

<length> = CHARINDEX(';', <revised_text>, 1) - 1

<revised_text> = SUBSTRING(Particulars, <start>, <len>)

				<start> = CHARINDEX(';',Particulars,1) + 1

				<len> = 99 (maximum)

Substituting these back into the original SUBSTRING function gives

SUBSTRING(
/* <text> */	particulars, 
/* <start > */	CHARINDEX(';', Particulars, 1) + 1,
/* <length> */	CHARINDEX(';', SUBSTRING(Particulars, 
					CHARINDEX(';', Particulars, 1) + 1, 
					99)) - 1 
)

This now only needs to be wrapped in LTRIM and RTRIM functions to remove leading and trailing blanks.

Additional code was also added to handle the condition where no second semi-colon was present.

Alternative Method

You can of course come up with other methods of doing this for example, the ELSE statement above can be substituted with:

-- else extract the full string from the first semi-colon up to second semi-colon
ELSE RTRIM(LTRIM(LEFT(
	-- This is the text following the first semi-colon up to the max length of 99
	SUBSTRING(Particulars,CHARINDEX(';',Particulars,1)+1,99), 
	-- This is the length, one character before the next semi-colon
	CHARINDEX(';',SUBSTRING(Particulars,CHARINDEX(';',Particulars,1)+1,99)) - 1
) ) ) 

Summary

You have seen demonstrated a method of extracting text from between two embedded delimiting characters. This has made use of the SUBSTRING, CHARINDEX, LTRIM and RTRIM functions.

I have searched for simpler ways to do this without resorting to a cursor, but as yet have not found one. Any alternative suggestions would be most welcome.

Total article views: 12217 | Views in the last 30 days: 12
 
Related Articles
BLOG

String Manipulation - CHARINDEX()

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

FORUM

substring

using substring to parse data from string

FORUM

SUBSTRING Question?

SUBSTRING

SCRIPT

Extend CHARINDEX with occurance matching

Retrieve the charindex of matching string for the desired occurrence

FORUM

extract XML values from multilanguage xml string storage

Alternatives to substring/charindex based extraction of XML value from string

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