January 27, 2006 at 3:00 pm
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
January 27, 2006 at 3:15 pm
Pinky, I have a question.
Will there always be 4 "-" dashes in your string between the "()" parenthesis?
or does that vary?
January 27, 2006 at 3:17 pm
Ray - there will be only 4 dashes in between. it will not vary
January 27, 2006 at 4:36 pm
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
January 27, 2006 at 4:49 pm
Ray - Thank u so much for ur help. Will try that and get back 2 u. It was a great help. i really appreciate
January 27, 2006 at 8:02 pm
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply