question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked BertZhoumsft-7490 commented

How to display values separated sticks on rows ?

I work on sql server 2017 i need to separate values separate stick to rows
as
below

create table #ChemicalConfilict
(
NULLflag varchar(300),
)
insert into #ChemicalConfilict(NULLflag)
values
('REACH Version|REACH Version|REACH|')

Expected result

  functionname
 REACH Version
 REACH Version
 REACH

so please how to get items separated stick to be every one on rows

can you help me on that please ?

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,@ahmedsalah-1628

Do you have further question on this , could we offer more support?
If this helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)

Bert Zhou

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

Since the order is not important, then try one of solutions:

 select s.[value] as functionname
 from #ChemicalConfilict c
 cross apply string_split(c.NULLflag, '|') s
 where s.[value] <> ''

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JingyangLi avatar image
0 Votes"
JingyangLi answered
 create table #ChemicalConfilict
 (
 NULLflag varchar(300),
 )
 insert into #ChemicalConfilict(NULLflag)
 values
 ('REACH Version|REACH Version|REACH|')
    
 select value as functionname 
 from #ChemicalConfilict
 cross apply openjson('["'+(replace(NULLflag,'|','","')+'"]'))
    
 drop table #ChemicalConfilict
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BertZhoumsft-7490 avatar image
0 Votes"
BertZhoumsft-7490 answered

Hi,@ahmedsalah-1628

Welcome to Microsoft T-SQL Q&A Forum!

If you want to continue inserting data in the future, and use“|” as the separator, then I provide a third different way of writing, and add new data to my sample data to test the results,
please see:

 create table #ChemicalConfilict
 (
 NULLflag varchar(300),
 )
 insert into #ChemicalConfilict(NULLflag)
 values
 ('REACH Version|REACH Version|REACH|adfds|sdfadgqer')
 select * from #ChemicalConfilict
    
  SELECT Split.a.value('.', 'VARCHAR(100)') AS functionname
  FROM  (SELECT  
          CAST ('<M>' + REPLACE(NULLflag, '|', '</M><M>') + '</M>' AS XML) AS String  
      FROM  #ChemicalConfilict) AS A 
 CROSS APPLY String.nodes ('/M') AS Split(a); 


Best regards,
Bert Zhou


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.




image.png (4.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.