How to display values separated sticks on rows ?

Question

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 ?

Answer 1

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] <> ''

Answer 2

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
 

Answer 3

Hi，@ahmed salah

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.