sql server - How to split a string to rows in SQL without function

Question

I have the following string

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

Each word in bold has to be a row. I am using the different recommendations, but I could only retrieve the first word, Technology. I need each bold words in a row. I need to approach this without function. I am using SQL Server 2012.

Answer 1

Try this:

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

Demo Result :http://rextester.com/UXDT75928