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