Extracting a String Within Delimeters - Part 2

Question

Post reply

Extracting a String Within Delimeters - Part 2

Lashams

Ten Centuries

Points: 1026
More actions
July 26, 2004 at 4:49 pm

#162324

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

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_string

FROM Sample_table

Code Monkey-158952 SSC Rookie Points: 37 More actions · Answer 2

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 ;

Best Regards,

Code Monkey

Adam Machanic SSCoach Points: 15370 More actions · Answer 3

Wouldn't it be easier to just use:

SELECT RIGHT(RTRIM(Particulars), LEN(RTRIM(Particulars)) - CHARINDEX(';',Particulars) - 1)

FROM Sample_table

Also, you could remove the RTRIMs if you used VARCHAR instead of CHAR...

--
Adam Machanic
whoisactive

Norbert L. Muth, M.A. SSC Enthusiast Points: 112 More actions · Answer 4

drop table Sample_table

CREATE TABLE #Sample_table (Particulars varchar(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')

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

--&nbsp rigth parenthesis for quotename

FROM #Sample_table

/* commet out ltrim/rtrim to extract the whole string with spaces

This 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.

sincerely

Norbert L.Muth, M.A.

DBA of KNO-VA.de

Norbert.Muth@gmx.de

*/

Lashams Ten Centuries Points: 1026 More actions · Answer 5

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.