DECLARE @Tabaldata TABLE ( data 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'

​

​

SELECT data ActualData, 

        SUBSTRING(data,CHARINDEX(';' ,data)+1,LEN(data)) AS ExpectedData

FROM

(

    SELECT Split.a.value('.','nvarchar(max)') data

    FROM(

    SELECT 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

​

​

​