Split field record into multiple fileds at second value occurrance 2

[ciarra41]

I'm trying to parse a field whereas at any second occurrance of a character value(') start from there and drop them into another field separately by ('). The below query works but keeps ASC the values

exp.
tr'1245'lt'ba'max'74
tr'1845'lkt'bf1a'7max'749


It needs to start at the second (') and put the remaining values in separate fields separated by (')

I tried this but it keeps puting the characters after the second(') in ascending order.





SELECT keyid, [1] as Mod1,[2] as Mod2,[3]as Mod3,[4] as Mod4
FROM (SELECT keyid, keytypes,
ROW_NUMBER() OVER(
PARTITION BY keyid ORDER BY keyid ) AS rownum
FROM (SELECT keyid, keytypes
FROM tablekeys ) AS D1) AS D2
PIVOT(MAX(keytypes)
FOR rownum IN([1],[2],[3],[4])) AS P

 

[drlex]

Using the technique in thread183-1669348 to split out your field:

DECLARE @test TABLE ( KeyID INT NOT NULL,
                       KeyTypes NVARCHAR(255) NOT NULL
					)
INSERT INTO @test
SELECT 1, 'tr''1245''lt''ba''max''74'
UNION ALL SELECT 2, 'tr''1845''lkt''bf1a''7max''749'

--SELECT * from @test
;with splitlist as 
(select KeyID,
ListItem,
ROW_NUMBER() OVER(PARTITION BY keyid ORDER by keyid) AS rownum
from @test
cross apply (select XMLEncoded=(select [*]=KeyTypes for xml path(''))) F0
cross apply (select TagsList='<x>'+replace(XMLEncoded,'''','</x><x>')+'</x>') F1
cross apply (select XmlList=cast(TagsList as xml)) F2
cross apply XmlList.nodes('x') F3(XmlNode)
cross apply (select ListItem=XmlNode.value('.','varchar(20)')) F4
)

SELECT keyid, [3]as Mod3,[4] as Mod4,[5] as Mod5,[6] as Mod6
FROM (SELECT keyid,  Listitem, rownum from splitlist) T1
PIVOT(Max(listItem) FOR rownum IN([3],[4],[5],[6])) AS P

Note that the recombination of the split list is hard-coded for only 4 columns, based on the example given.


soi là, soi carré

 

[ciarra41]

Super but I need to add '>' as the separate where do I add that?

 

[drlex]

Thanks for the star; the separator ' is in this section

[...]
cross apply (select TagsList='<x>'+replace(XMLEncoded,'[b][red]''[/red][/b]','</x><x>')+'</x>') F1
[...]

(the ' is repeated, as it's the text delimiter - see also the INSERT INTO section.)

Change to

[...]
cross apply (select TagsList='<x>'+replace(XMLEncoded,'[b][red]>[/red][/b]','</x><x>')+'</x>') F1
[...]

If it's in addition to the ', then you'll need to add in a REPLACE function on the relevant field.


soi là, soi carré

 

[ciarra41]

hmm,
that did not work
Here's how the field look:
>59>rt>mb>50

 

[markros]

Can you post your exact data again? Are you saying that instead of ' separator you're using > as a separator? I think it will probably get already encoded then, so most likely this method of splitting will not work for you.

Instead you can get any other way of splitting and then simply PIVOT, e.g.

http://www.sqlservercentral.com/articles/Tally+Table/72993/

PluralSight Learning Library

 

[markros]

If you can use the above method as is, then drop the first CROSS APPLY - this will work as long as you don't use other special characters such as &

PluralSight Learning Library

 

[drlex]

As markros points out, a < or > separator won't work with these XML commands, so use REPLACE to change it in the F0 part of the splitlist CTE to match that character specified in the REPLACE part in F1.
(On mobile device, so can't C&P code)

soi là, soi carré

 

[ciarra41]

Thanks to everyone, you guys are awesome. I added the replace string; bingo!