Parsing Data --Need help plz

Question

Post reply

Parsing Data --Need help plz

pinky

SSCommitted

Points: 1997
More actions
January 27, 2006 at 3:00 pm

#90522

Hi i have posted an issue yesterday regarding this and got it solved, but came up with one more question. In a particular field i have data some what like this
abcdefg(DFT-VHSDH DEL-DEST P & A-015-)
SDFDGG DFGDG(DFG-QNDFJHG-HSJA-346-SFF)
I need to pull the data lying within the brackets in separate fields. i.e
DFT VHSDH DEL DEST P& A 015
DFG QNDFJHG HSJA 346 SFF
I could pull the three numbers i.e 015 in separate field with the solution i got from Ray yesterday. but can anyone please help me out and let me know if i can parse the data in separate fields. I have only 4 dashes '-' separating those within the braces. Any help is appreciated
Thank u so much

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

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

Ray M SSC-Insane Points: 21093 More actions · Answer 1

Pinky, I have a question.

Will there always be 4 "-" dashes in your string between the "()" parenthesis?

or does that vary?

pinky SSCommitted Points: 1997 More actions · Answer 2

Ray - there will be only 4 dashes in between. it will not vary

Ray M SSC-Insane Points: 21093 More actions · Answer 3

Sorry it took so long.

Couldn't decide how I wanted to do it.

I created a function that takes the field to parse, and the segment you want.

IF EXISTS (SELECT *

FROM sysobjects

WHERE name = N'udf_parseString')

DROP FUNCTION udf_parseString

GO

CREATE FUNCTION udf_parseString

(@String varchar(50), @FieldNumber smallint)

RETURNS varchar(50)

AS

BEGIN

Declare @Field varchar(50)

select @String = substring(@String,charindex('(', @String) + 1, (charindex(')',@String) - charindex('(', @String))-1)

if @FieldNumber = 2

select @String = substring(@String, charindex('-', @String) + 1, 50)

else if @FieldNumber = 3

select @String = substring(@String, charindex('-', @String, charindex('-', @String) + 1) + 1, 50)

else if @FieldNumber = 4

select @String = substring(@String, charindex('-', @String, charindex('-', @String, charindex('-', @String) + 1) + 1) + 1, 50)

else if @FieldNumber = 5

select @String = reverse(substring(reverse(@String),1,charindex('-',reverse(@String))-1)) + '-'

if len(@String) <=1

set @Field = ''

else

set @Field = substring(@String, 1, charindex('-', @String)-1)

Return @Field

END

GO

drop table #testtable

Create table #testtable (pk int identity, Field varchar(50))

insert into #TestTable (Field)

values ('abcdefg(DFT-VHSDH DEL-DEST P & A-015-)')

insert into #TestTable (Field)

values ('SDFDGG DFGDG(DFG-QNDFJHG-HSJA-346-SFF)')

select Field,

dbo.udf_parseString(Field,1) as Field1,

dbo.udf_parseString(Field,2) as Field2,

dbo.udf_parseString(Field,3) as Field3,

dbo.udf_parseString(Field,4) as Field4,

dbo.udf_parseString(Field,5) as Field5

from #TestTable

Results

Field,Field1,Field2,Field3,Field4,Field5

abcdefg(DFT-VHSDH DEL-DEST P & A-015-),DFT,VHSDH DEL,DEST P & A,015,

SDFDGG DFGDG(DFG-QNDFJHG-HSJA-346-SFF),DFG,QNDFJHG,HSJA,346,SFF

pinky SSCommitted Points: 1997 More actions · Answer 4

Ray - Thank u so much for ur help. Will try that and get back 2 u. It was a great help. i really appreciate

pinky SSCommitted Points: 1997 More actions · Answer 5

Hey sorry to get back again. Your code works perfect, but i was not clear to you. actually my table looks somewhat like this

Field	ID
abcdefg(DFT-VHSDH DEL-DEST P & A-015-)	LOC
SDFDGG DFGDG(DFG-QNDFJHG-HSJA-346-SFF)	LOC
SDFDGG DFGDG - ABC FLT ID-47646734	MON
SONOCO CORP - XYZ FLT ID-655688	MON

In the field column i have two different descriptions. if the ID is like 'LOC' i need to insert the data in five different fields. else if it is 'MON' i want to leave it as NULL

My output should look like this

Field	ID	F1	F2	F3	F4	F5
abcdefg(DFT-VHSDH DEL-DEST P & A-015-)	LOC	DFT	VHSDH DEL	DEST P & A	015
SDFDGG DFGDG(DFG-QNDFJHG-HSJA-346-SFF)	LOC	DFG	QNDFJHG	HSJA	346	SFF
SDFDGG DFGDG - ABC FLT ID-47646734	MON
SONOCO CORP - XYZ FLT ID-655688	MON

Thanks in advance for your help.

DFT	VHSDH DEL	DEST P& A	015
DFG	QNDFJHG	HSJA	346	SFF