Break Apart Data Using While Loop

I need to break apart the following data into multiple records but I am not
sure how to write the code. The record identifier is the ;
RecordID DataInfo
1 "abc", "def"; "ghi", "jkl"
2 "abc", "def", "ghi"; "jkl", "mno"
Data in a new table will be
RecordID DataInfo
1 "abc", "def"
1 "ghi", "jkl"
2 "abc", "def", "ghi"
2 "jkl", "mno"
Thanks for the help!If the delimiter ; only appears once in the DataInfo column you could
do something like this...
Create Table #TableA
(
Record Integer,
DataInfo varchar(50)
)
Insert Into #TableA
Select 1, '"abc", "def"; "ghi", "jkl"'
Union
Select 2, '"abc", "def", "ghi"; "jkl", "mno" '
Create Table #TableB
(
Record Integer,
DateInfo Varchar(50)
)
Insert Into #TableB
Select Record, Replace((Ltrim(Rtrim(Left(DataInfo,
(CharIndex(';',DataInfo)))))), ';','')
>From #TableA
Union
Select Record, Replace((Ltrim(Rtrim(Right(DataInfo,
(CharIndex(';',Reverse(DataInfo))))))), ';','')
>From #TableA
Select * From #TableA
Select * From #TableB
Drop Table #TableA
Drop Table #TableB
HTH
Barry|||Sorry my example wasn't specific enough. The delimiter can appear multiple
times in the column.
"Barry" wrote:

> If the delimiter ; only appears once in the DataInfo column you could
> do something like this...
>
> Create Table #TableA
> (
> Record Integer,
> DataInfo varchar(50)
> )
> Insert Into #TableA
> Select 1, '"abc", "def"; "ghi", "jkl"'
> Union
> Select 2, '"abc", "def", "ghi"; "jkl", "mno" '
> Create Table #TableB
> (
> Record Integer,
> DateInfo Varchar(50)
> )
>
> Insert Into #TableB
> Select Record, Replace((Ltrim(Rtrim(Left(DataInfo,
> (CharIndex(';',DataInfo)))))), ';','')
> Union
> Select Record, Replace((Ltrim(Rtrim(Right(DataInfo,
> (CharIndex(';',Reverse(DataInfo))))))), ';','')
>
> Select * From #TableA
> Select * From #TableB
> Drop Table #TableA
> Drop Table #TableB
>
> HTH
> Barry
>|||
As an alternative you can use a numbers table
as in http://www.aspfaq.com/show.asp?id=2516
and can do this
select Record,
ltrim(substring(DataInfo,
Number,
charindex(';',
DataInfo + ';',
Number) - Number)) as DataInfo
from #TableA
inner join Numbers on Number between 1 and len(DataInfo) + 1
and substring(';' + DataInfo, Number, 1) = ';'|||Or regular expressions...
http://www.sqlservercentral.com/col...oolkitpart2.asp
"Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
news:37F08AA1-FC30-42C9-B50F-50B0FA9FD7B5@.microsoft.com...
>I need to break apart the following data into multiple records but I am not
> sure how to write the code. The record identifier is the ;
> RecordID DataInfo
> 1 "abc", "def"; "ghi", "jkl"
> 2 "abc", "def", "ghi"; "jkl", "mno"
> Data in a new table will be
> RecordID DataInfo
> 1 "abc", "def"
> 1 "ghi", "jkl"
> 2 "abc", "def", "ghi"
> 2 "jkl", "mno"
> Thanks for the help!
>