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


SELECT Particulars,
	-- 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
       /* <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



LDR ; LUC20031026901 ; Vehicle


LDR ; Consolidated


LDR ; SUB35030172701 ; Building


LDR ; LIQ200310121 ; Liquor


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.

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

/* <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
	-- This is the text following the first semi-colon up to the max length of 99
	-- This is the length, one character before the next semi-colon
	CHARINDEX(';',SUBSTRING(Particulars,CHARINDEX(';',Particulars,1)+1,99)) - 1
) ) ) 


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: 12264 | Views in the last 30 days: 11
Related Articles

String Manipulation - CHARINDEX()

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



using substring to parse data from string





Extend CHARINDEX with occurance matching

Retrieve the charindex of matching string for the desired occurrence


Problem segregating third occurence from a column

Charindex ,substring

advanced querying    

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

Already a member? Jump in:

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