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.

Is this big value in a field already in the database or is this in a flat file that you are trying to import? — JNevill, Jun 08 '18 at 13:30
[You can use this solution](https://stackoverflow.com/a/5722833/6167855) and replace the space with a pipe. — S3S, Jun 08 '18 at 13:31
What do you want your output to look like? Is it just this one string or is there a table full of them? — MJH, Jun 08 '18 at 13:35
http://eval-sql.net/sql-server-regex - maybe this would help - have never used it to vouch for it. But seems viable - without knowing what output you are expecting? — JGFMK, Jun 08 '18 at 13:36
It is a value already in the database. The database is PWA. I cannot use a function because I do not have admin rights to create a function in the database. — marod1971, Jun 08 '18 at 19:24

iamdave · Answer 1 · 2018-06-08T14:21:37.530

Using the answer linked to by scsimon, you can use the following script to extract the bolded words in your question:

-- Test table
declare @t table (Id int identity(1,1), Col varchar(1000))
insert into @t(Col) values ('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')
                          ,('asd|a;dse|a;gggg|a')

select t.Id
        ,n.r.value('.', 'varchar(50)') as String
        ,left(n.r.value('.', 'varchar(50)'),charindex('|',n.r.value('.', 'varchar(50)'),1)-1) as Words
        ,substring(n.r.value('.', 'varchar(50)'),charindex('|',n.r.value('.', 'varchar(50)'),1)+1,999999) as GUIDs
from @t as t
  cross apply (select cast('<r>'+replace(replace(Col,'&','&amp;'), ';', '</r><r>')+'</r>' as xml)) as S(XMLCol)
  cross apply S.XMLCol.nodes('r') as n(r)
order by t.Id
        ,Words;

Output:

+----+----------------------------------------------------+------------------+--------------------------------------+
| Id |                       String                       |      Words       |                GUIDs                 |
+----+----------------------------------------------------+------------------+--------------------------------------+
|  1 | Application|56b19e9a-e58a-4c79-a518-b129fb5f499f   | Application      | 56b19e9a-e58a-4c79-a518-b129fb5f499f |
|  1 | Database|d7425391-8f8c-4aec-be04-9caf2f55584a      | Database         | d7425391-8f8c-4aec-be04-9caf2f55584a |
|  1 | Mobile/BYOD|8f0f30e7-d16d-48a3-ad82-cfdd39156760   | Mobile/BYOD      | 8f0f30e7-d16d-48a3-ad82-cfdd39156760 |
|  1 | Networking|3876dbd8-8cd8-4040-9c67-0633f8477f93    | Networking       | 3876dbd8-8cd8-4040-9c67-0633f8477f93 |
|  1 | Operating System|10fc2ce4-53fd-4af2-8fd9-9df66a387 | Operating System | 10fc2ce4-53fd-4af2-8fd9-9df66a387    |
|  1 | Reporting|00307182-43f4-4bbf-9a95-cd8dbf59754a     | Reporting        | 00307182-43f4-4bbf-9a95-cd8dbf59754a |
|  1 | Security|014e8d4d-4fd9-404c-8db8-13e84c9042fe      | Security         | 014e8d4d-4fd9-404c-8db8-13e84c9042fe |
|  1 | Technology|faa5d304-f2d1-42c3-8d21-e87697b42bdc    | Technology       | faa5d304-f2d1-42c3-8d21-e87697b42bdc |
|  1 | User Interface|57d65a47-6ad2-4df7-8d36-acdf3e0a314 | User Interface   | 57d65a47-6ad2-4df7-8d36-acdf3e0a314  |
|  1 | Web Tech|1b9e82eb-5f70-4183-9093-5                 | Web Tech         | 1b9e82eb-5f70-4183-9093-5            |
|  2 | asd|a                                              | asd              | a                                    |
|  2 | dse|a                                              | dse              | a                                    |
|  2 | gggg|a                                             | gggg             | a                                    |
+----+----------------------------------------------------+------------------+--------------------------------------+

I would like to know if you have an idea how to do it for a specific column database. Thanks. — marod1971, Jun 14 '18 at 15:22
What do you mean by column database? The script already works on a specific table column — iamdave, Jun 14 '18 at 15:46
@marod1971 Oh, do you mean to only pull out the value for `Database` in the `Words` column of my output? If so, just add a filter for `where Words = 'Database'` — iamdave, Jun 14 '18 at 16:01

score 1 · Answer 2 · edited Jun 08 '18 at 14:19

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

edited Jun 08 '18 at 14:19

Zoe

27,060
21
118
148

answered Jun 08 '18 at 14:16

Sreenu131

2,476
1
7
18

Thanks, it is the best approach that I am looking for. – marod1971 Jun 21 '18 at 14:30
1

@marod1971 You say *Thanks, it is the best approach that I am looking for*. It is kind to say "Thanks", but the SO-way of saying "thx" is the up-vote. And - additionally - if this answer helped you, please accept it. This will mark this quesiton as closed and will pay points to Sreenu131 and to your own account. – Shnugo Jun 22 '18 at 08:54
@shnugo I do not have enough reputation to cast my vote, but I cast my vote today. – marod1971 Jun 22 '18 at 13:38

How to split a string to rows in SQL without function

2 Answers2

Linked