-2

How can I convert comma separated value as :

Table

User Name  Unit
ABC        2,3

to the following :

User Name  Unit
ABC        2  
ABC        3
Ullas
  • 11,450
  • 4
  • 33
  • 50
Furqan Misarwala
  • 1,743
  • 6
  • 26
  • 53

4 Answers4

4

You have tagged your question with SQL Server 2016, in SQL Server 2016 there is a new function STRING_SPLIT.

In SQL Server 2016 your query should be as simple as:

declare @tab table ([user_name] varchar(10),Unit varchar(100))
insert into @tab
VALUES ('ABC','1,2')

SELECT t.[user_name]
       , Value as Unit
FROM @tab t 
    CROSS APPLY STRING_SPLIT(t.Unit , ',')
M.Ali
  • 67,945
  • 13
  • 101
  • 127
2

In SQL server below will be the logic Hope it helps

declare @tab table ([user_name] varchar(10),val varchar(100))
insert into @tab
select 'ABC','1,2'

SELECT A.[user_name],  
     Split.a.value('.', 'VARCHAR(100)') AS String  
 FROM  (SELECT [user_name],  
         CAST ('<M>' + REPLACE(val, ',', '</M><M>') + '</M>' AS XML) AS String  
     FROM  @tab) AS A CROSS APPLY String.nodes ('/M') AS Split(a);

output

user_name   String
ABC          1
ABC          2
Ajay
  • 764
  • 4
  • 12
-1
CREATE FUNCTION dbo.SplitCSVs (@CSVString varchar(1000))

RETURNS @SeparatedValues TABLE (Value VARCHAR(100))
AS
BEGIN
 DECLARE @CommaPosition INT
 WHILE (CHARINDEX(',', @CSVString, 0) > 0)
 BEGIN
  SET @CommaPosition = CHARINDEX(',', @CSVString, 0)
  INSERT INTO @SeparatedValues (Value)
  SELECT SUBSTRING(@CSVString, 0, @CommaPosition)
  SET @CSVString = STUFF(@CSVString, 1, @CommaPosition, '')
 END
  INSERT INTO @SeparatedValues (Value)
  SELECT @CSVString

 RETURN
END

SELECT * FROM dbo.SplitCSVs('aby,123')
Rob
  • 26,989
  • 16
  • 82
  • 98
Aby C Paul
  • 11
  • 3
  • Welcome to Stack Overflow! Please don't add [the same answer](https://stackoverflow.com/a/52400332/4733879) to multiple questions. Answer the best one and flag the rest as duplicates. See [Is it acceptable to add a duplicate answer to several questions?](http://meta.stackexchange.com/q/104227/347985) – Filnor Sep 19 '18 at 07:27
  • 1
    Welcome! Please note that [overt self-promotion is not allowed here](https://stackoverflow.com/help/behavior). – S.L. Barth is on codidact.com Sep 19 '18 at 07:42
-2

You can use the fn_split function.

inadvisableguy
  • 107
  • 2
  • 12