November 10, 2014 at 1:22 pm
Hi,
I am trying to parse data separated through text (ie abc1, abc2, abc3, abc4, etc).
ID ParseData
1 [abc1.Pants/abc2.Orange hat /abc3.Purple shirt /abc4./abc5./abc6./abc7./abc8.]
2 [abc1.Gray Shoes/abc2.Striped jacket /abc3./abc4./abc5./abc6./abc7./abc8.]
3 [abc1.Blue jeans/abc2./abc3./abc4./abc5./abc6./abc7./abc8.]
New Data (abc1, abc2, abc3, etc each have a field in the new data set)
ID ParseData abc1 abc2 abc3 abc4 abc5 abc6 abc7 abc8
1 [abc1.Pants...abc8.] Pants Orange hat Purple shirt
2 [abc1.Gray...abc8.] Gray Shoes Striped jacket
3 [abc1.Blue...abc8.] Blue Jeans
If I only want the data in between abc1 and abc2, between abc2 and abc3, etc, what would be the best way to do that?
My code so far looks like:
DECLARE
@string varchar(100) = '[abc1.Pants/abc2.Orange hat /abc3.Purple shirt /abc4./abc5./abc6./abc7./abc8.]',
@searchString1 varchar(20) = 'abc1',
@searchString2 varchar(20) = 'abc2';
SELECT newstring
FROM dbo.SubstringBetween(@string,@searchString1,@searchString2);
This returns 'Pants.'
How do I continue to parse between abc2 and abc3? between abc3 and abc4?
And then continue to ID2?
Should I be referencing the ParseData field instead of string of data that I want to parse?
Please let me know if you need more info.
Thanks for your help!
November 10, 2014 at 2:27 pm
You don't need a loop. You need a really fast splitter which you can find in the following article: http://www.sqlservercentral.com/articles/Tally+Table/72993/
Once you split the data, you just need some cross tabs and you're done.
CREATE TABLE SampleData(
ID int,
ParseData varchar(8000))
INSERT INTO SampleData VALUES
(1, '[abc1.Pants/abc2.Orange hat /abc3.Purple shirt /abc4./abc5./abc6./abc7./abc8.]'),
(2, '[abc1.Gray Shoes/abc2.Striped jacket /abc3./abc4./abc5./abc6./abc7./abc8.]'),
(3, '[abc1.Blue jeans/abc2./abc3./abc4./abc5./abc6./abc7./abc8.]')
SELECT ID,
ParseData,
MAX(CASE WHEN ItemNumber = 1 THEN PARSENAME( Item, 1) END) abc1,
MAX(CASE WHEN ItemNumber = 2 THEN PARSENAME( Item, 1) END) abc2,
MAX(CASE WHEN ItemNumber = 3 THEN PARSENAME( Item, 1) END) abc3,
MAX(CASE WHEN ItemNumber = 4 THEN PARSENAME( Item, 1) END) abc4,
MAX(CASE WHEN ItemNumber = 5 THEN PARSENAME( Item, 1) END) abc5,
MAX(CASE WHEN ItemNumber = 6 THEN PARSENAME( Item, 1) END) abc6,
MAX(CASE WHEN ItemNumber = 7 THEN PARSENAME( Item, 1) END) abc7,
MAX(CASE WHEN ItemNumber = 8 THEN PARSENAME( Item, 1) END) abc8
FROM SampleData d
CROSS APPLY (SELECT REPLACE( REPLACE( ParseData, '[', ''), ']', '') ParsedData) p
CROSS APPLY dbo.DelimitedSplit8K( p.ParsedData, '/') split
GROUP BY ID,
ParseData
ORDER BY ID
GO
DROP TABLE SampleData
See how I posted the sample data in a way that anyone can copy it and use it without effort? You should be doing it the same way to help us.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply