SQLServerCentral / Discuss Content Posted by Stephen Lasham / Article Discussions / Article Discussions by Author / Extracting a String Within Delimeters - Part 2 / Latest Posts

RE: Extracting a String Within Delimeters - Part 2

Lashams — Tue, 14 Sep 2004 14:49:00 GMT

Thanks for the feedback. The typo occurred in translation from the submitted MS/Word document to the HTML page and was an accident by the publisher that I did not pick up on. Please accept my apologies for this.

RE: Extracting a String Within Delimeters - Part 2

Norbert L. Muth, M.A. — Mon, 13 Sep 2004 16:27:00 GMT

drop table Sample_tableCREATE TABLE #Sample_table (Particulars varchar(120))GOINSERT 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')

select * from #Sample_table

SELECT Particulars,-- quotename( -- enable this to see, that's just the string without spaces around LTRIM(RTRIM( SUBSTRING(Particulars, /*from*/CHARINDEX(';',Particulars,1)+1, -- search first delimiter and then offset 1 from first deliminter

/* to */CHARINDEX(';', -- search for the next delimter as the end-of-string SUBSTRING (Particulars, /*from*/ (CHARINDEX (';', Particulars,1)+1) -- the first character after first delimiter /* to */ , datalength(Particulars) -- use datalength for not beeing bound to specific length ) + ';' -- for any case add a delimiter (this was a good idea from Denis Oliynik !!), if it's an additonal, it's just lost ) - 1 -- delimiter Position minus one ) ) -- rigth parenthesis for lrim ) -- rigth parenthesis for rtrim-- rigth parenthesis for quotenameFROM #Sample_table

/* commet out ltrim/rtrim to extract the whole string with spacesThis is a very general select not delimited to programmed stringlenthes or varchar's etc. It just extracts the first delimited string within another string, not more or less.

sincerelyNorbert L.Muth, M.A.DBA of KNO-VA.deNorbert.Muth@gmx.de*/

RE: Extracting a String Within Delimeters - Part 2

Adam Machanic — Mon, 13 Sep 2004 09:46:00 GMT

Wouldn't it be easier to just use:SELECT RIGHT(RTRIM(Particulars), LEN(RTRIM(Particulars)) - CHARINDEX(';',Particulars) - 1)FROM Sample_tableAlso, you could remove the RTRIMs if you used VARCHAR instead of CHAR...

RE: Extracting a String Within Delimeters - Part 2

Code Monkey-158952 — Mon, 13 Sep 2004 08:01:00 GMT

The innermost SUBSTRING will yield this:

LUC20031026901 ; Vehicle Consolidated SUB35030172701 ; Building LIQ200310121 ; Liquor

and when the semicolon is added, it will look like this:

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

RE: Extracting a String Within Delimeters - Part 2

Antares686 — Mon, 13 Sep 2004 06:16:00 GMT

There is a typo in the SELECT code, needs to be

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

Extracting a String Within Delimeters - Part 2

Lashams — Mon, 26 Jul 2004 16:49:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/colu