Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DECLARE @Tabaldata TABLE ( data nvarchar(max), PM nvarchar(max))
- INSERT INTO @Tabaldata
- SELECT
- 'Technology|faa5d304-f2d1-42c3-8d21-e87697b42bdc;Application|56b19e9a-e58a-4c79-a518-b129fb5f499f;Database
- |d7425391-8f8c-4aec-be04-9caf2f55584a;Mobile/BYOD|8f0f30e7-d16d-48a3-ad82-cfdd39156760;Networking
- |3876dbd8-8cd8-4040-9c67-0633f8477f93;Operating System|10fc2ce4-53fd-4af2-8fd9-9df66a38715f;Reporting|
- 00307182-43f4-4bbf-9a95-cd8dbf59754a;Security|014e8d4d-4fd9-404c-8db8-13e84c9042fe;User Interface|57d65a47-6ad2-4df7-8d36-acdf3e0a3145;Web Tech|1b9e82eb-5f70-4183-9093-5', 'Testing Project'
- SELECT PM, data ActualData,
- SUBSTRING(data,CHARINDEX(';' ,data)+1,LEN(data)) AS ExpectedData
- FROM
- (
- SELECT PM, Split.a.value('.','nvarchar(max)') data
- FROM(
- SELECT PM, CAST('<S>'+REPLACE(data,'|','</S><S>')+'</S>' AS XML) data
- FROM @Tabaldata
- )AS A
- CROSS APPLY data.nodes('S') AS Split(a)
- )dt
- WHERE PATINDEX('%[0-9]%',(SUBSTRING(data,CHARINDEX(';' ,data)+1,LEN(data))))=0
Add Comment
Please, Sign In to add comment