Parse Semi colon data in seperate columns

Question

Hi:

I have semi-colon separated data in one column and I would like to split that in separate columns.

CREATE TABLE #tblTest  
(FileData varchar(MAX))  
  
INSERT INTO #tblTest values ('TEST1;Testing 123;')  
INSERT INTO #tblTest values ('TEST2;Testing.234;')  
INSERT INTO #tblTest values ('TEST3;Testing 345;')  
INSERT INTO #tblTest values ('TEST4;Testing 456;')  
INSERT INTO #tblTest values ('TEST5;Testing5.67;')  
  
SELECT * FROM #tblTest  

EXPECTED Output:

Thanks!

Answer 1

Hi @SQL ,

The following is a general custom function that can be used to divide a column into multiple columns or multiple rows.Please refer to:

create function dbo.SplitSubString   
(   
 @Expression varchar(8000)   
,@Delimiter varchar(100)   
,@ int   
)   
returns varchar(8000)   
as   
begin   
  
  
declare @p int   
set @p = CharIndex(@Delimiter,@Expression)   
if @p > 0  
begin  
   set @p = @p + len(@Delimiter) - 1  
end  
declare @i int   
set @i = 1   
while @i < @   
begin    
   set @i = @i + 1  
   set @Expression = substring (@Expression, @p + 1,len(@Expression) - @p )   
   set @p = CharIndex(@Delimiter,@Expression)  
   if @p > 0  
   begin  
      set @p = @p + len(@Delimiter) - 1  
   end  
   else  
   begin  
      break  
   end  
end   
  
declare @s varchar(1000)   
if @p = 0 and @i = @  
begin   
   set @s = @Expression  
end   
else if @i = @   
begin   
   set @s = substring(@Expression, 1,@p - len(@Delimiter))   
end   
return @s  
end   
  
GO  
  
select   
TestDB.dbo.SplitSubString(FileData,';',1) as CODE,TestDB.dbo.SplitSubString(FileData,';',2) as Label  
from #tblTest  
  
drop function dbo.SplitSubString   

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Best Regards
Echo

Answer 2

Unfortunately, this forum doesn't allow to provide source code level solution.
Direct typing and attachment, both didn't work for me.

Access Denied
You don't have permission to access "http://learn.microsoft.com/answers/answers/80564/post.html" on this server.
Reference #18.1411e8ac.1598648531.1e72a5ab

Answer 3

We are on SQL 2016

Answer 4

SELECT  FileData, 
        Code = CASE WHEN CHARINDEX(';', FileData) > 0 THEN SUBSTRING(FileData, 1, CHARINDEX(';', FileData) - 1) ELSE FileData END,
        Label = CASE WHEN CHARINDEX(';', FileData) > 0 THEN SUBSTRING(FileData, CHARINDEX(';', FileData) + 1, LEN(FileData) - CHARINDEX(';', FileData)) ELSE '' END
FROM #tblTest;

Answer 5

CREATE TABLE #tblTest
 (FileData varchar(MAX))

 INSERT INTO #tblTest values ('TEST1;Testing 123;')
 INSERT INTO #tblTest values ('TEST2;Testing.234;')
 INSERT INTO #tblTest values ('TEST3;Testing 345;')
 INSERT INTO #tblTest values ('TEST4;Testing 456;')
 INSERT INTO #tblTest values ('TEST5;Testing5.67;')

 SELECT  FileData,
 JSON_VALUE ('{"vals":["'+(replace(FileData,';','","')+' "]}'),'$.vals[0]') Code,
 JSON_VALUE ('{"vals":["'+(replace(FileData,';','","')+' "]}'),'$.vals[1]') Label

  FROM #tblTest


 drop TABLE #tblTest