SQL how to delimit the value from row to column

Question

hi,

how to split the value in cloumns

select value from STRING_SPLIT('apple\banana\lemon\kiwi\orange\coconut','\')

column Fruit, value 'apple\banana\lemon\kiwi\orange\coconut'

Result looking for :

column level1, value = 'apple'

column level2, value = banana

column level3, value = lemon

column level4, value = kiwi

column level5, value = orange

and so on

Answer 1

Hi @Kurian, Ranjith C SBOBNG-ITV/SER ,

Thank you so much for posting here.

You could try with one good split user defined function as below:

CREATE FUNCTION dbo.GetSplitString  
(  
   @List       VARCHAR(MAX),  
   @Delimiter  VARCHAR(255),  
   @ElementNumber int  
)  
RETURNS VARCHAR(4000)  
AS  
BEGIN  
   DECLARE @result varchar(4000)      
   DECLARE @Items TABLE ( position int IDENTITY PRIMARY KEY,  
                          Item VARCHAR(4000)  
                         )    
   DECLARE @ll INT = LEN(@List) + 1, @ld INT = LEN(@Delimiter);   
   WITH a AS  
   (  
       SELECT  
           [start] = 1,  
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter,   
                       @List, @ld), 0), @ll),  
           [value] = SUBSTRING(@List, 1,   
                     COALESCE(NULLIF(CHARINDEX(@Delimiter,   
                       @List, @ld), 0), @ll) - 1)  
       UNION ALL  
       SELECT  
           [start] = CONVERT(INT, [end]) + @ld,  
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter,   
                       @List, [end] + @ld), 0), @ll),  
           [value] = SUBSTRING(@List, [end] + @ld,   
                     COALESCE(NULLIF(CHARINDEX(@Delimiter,   
                       @List, [end] + @ld), 0), @ll)-[end]-@ld)  
       FROM a  
       WHERE [end] < @ll  
   )  
   INSERT @Items SELECT [value]  
   FROM a  
   WHERE LEN([value]) > 0  
   OPTION (MAXRECURSION 0);  

   SELECT @result=Item  
   FROM @Items  
   WHERE position=@ElementNumber  
   RETURN @result;  
END  
GO  

Then you could call this function as below:

declare @value varchar(1000)='apple\banana\lemon\kiwi\orange\coconut'  

SELECT dbo.GetSplitString(@value,'\',1) level1  
,dbo.GetSplitString(@value,'\',2) level2  
,dbo.GetSplitString(@value,'\',3) level3  
,dbo.GetSplitString(@value,'\',4) level4  
,dbo.GetSplitString(@value,'\',5) level5  
,dbo.GetSplitString(@value,'\',6) level6  

Output:

level1  level2  level3  level4  level5  level6  
apple   banana  lemon   kiwi    orange  coconut  

Or you could also have a dynamic way whatever how many '\' you have in the string as below:

declare @value varchar(1000)='apple\banana\lemon\kiwi\orange\coconut'  

declare @n int=1  
declare @num int  
select @num =LEN(@value) - LEN(REPLACE(@value,'\',''))  

declare @sql nvarchar(max)=''  
declare @statement nvarchar(max)='SELECT '  

WHILE ( @n <= @num+1)  
BEGIN  
    SET @sql= 'dbo.GetSplitString('''+@value+''',''\'','+trim(cast(@n as char(2)))+') level'++trim(cast(@n as char(2)))+','  
    SET @n  = @n  + 1  
    SET @statement=@statement+@sql  
END  

SET @statement=SUBSTRING(@statement,1,len(@statement)-1)  

EXECUTE sp_executesql   @statement    

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
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.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 2

Thank you all for the solution, it helped me a lot...

Answer 3

Hi @Kurian, Ranjith C SBOBNG-ITV/SER ,

You could also try with PIVOT method as below:

;with cte as   
(select value,ROW_NUMBER() OVER (order by (select null)) as rn from STRING_SPLIT('apple\banana\lemon\kiwi\orange\coconut','\'))  
 select [1] as level1,[2] as level2,[3] as level3,[4] as level4,[5] as level5,[6] as level6 from   
 (  
 select * from cte a  
 pivot  
(max(value) for rn in ([1],[2],[3],[4],[5],[6])) p) b  

Output:

level1 level2 level3 level4 level5 level6  
apple banana lemon kiwi orange coconut  

Or dynamic way as below:

declare @value varchar(1000)='apple\banana\lemon\kiwi\orange\coconut'  
declare @delimiter char(1)='\'  
declare @num int  
select @num =LEN(@value) - LEN(REPLACE(@value,@delimiter,''))  
  
declare @sql nvarchar(max)=''  
declare @sql1 nvarchar(max)=''  
declare @statement nvarchar(max)=''  
declare @statement1 nvarchar(max)=''  
declare @statement2 nvarchar(max)=''  
  
declare @n int=1  
WHILE ( @n <= @num+1)  
 BEGIN  
     SET @sql= '['+trim(cast(@n as char(2)))+'],'  
	 SET @sql1 ='['+trim(cast(@n as char(2)))+']' +' as level'+trim(cast(@n as char(2)))+','  
     SET @n  = @n  + 1  
     SET @statement=@statement+@sql  
	 SET @statement1=@statement1+@sql1  
 END  
  
 set @statement=SUBSTRING(@statement,1,LEN(@statement)-1)  
 set @statement1=SUBSTRING(@statement1,1,LEN(@statement1)-1)  
  
 set @statement2=N';with cte as   
(select value,ROW_NUMBER() OVER (order by (select null)) as rn from STRING_SPLIT('''+@value+''','''+@delimiter+'''))  
 select '+@statement1+' from   
 (  
 select * from cte a  
 pivot  
(max(value) for rn in ('+@statement+')) p) b  
'  
EXECUTE sp_executesql  @statement2  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
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.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 4

SQL Server 2016 onwards. Very simple method by using JSON .

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, Fruit VARCHAR(100));
INSERT INTO @tbl (Fruit) VALUES
('apple\banana\lemon\kiwi\orange\coconut');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = '\';

;WITH rs AS
(
 SELECT * 
 , tokens = '["' + REPLACE(Fruit, @separator, '","') + '"]'
 FROM @tbl
)
SELECT ID
 , JSON_VALUE(tokens, '$[0]') AS [level1]
 , JSON_VALUE(tokens, '$[1]') AS [level2]
 , JSON_VALUE(tokens, '$[2]') AS [level3]
 , JSON_VALUE(tokens, '$[3]') AS [level4]
 , JSON_VALUE(tokens, '$[4]') AS [level5]
 , JSON_VALUE(tokens, '$[5]') AS [level6]
FROM rs;

Output

+----+--------+--------+--------+--------+--------+---------+
| ID | level1 | level2 | level3 | level4 | level5 | level6  |
+----+--------+--------+--------+--------+--------+---------+
|  1 | apple  | banana | lemon  | kiwi   | orange | coconut |
+----+--------+--------+--------+--------+--------+---------+