How to split string in a column then store the data by column in T-SQL?

Question

I want to achieve a splitting the data in a string then store the split string in respected column. See below example. I am using MS SQL Server. I want to use SPLIT_STRING() is that possible?

Bad Result:
Id  Code    Room    Class
75  4       28      D
76  6       20      A
77  7       29      B
78  2       39      C
79  5 47 F  NULL    NULL

Good Result:
Id  Code    Room    Class
75  4       28      D
76  6       20      A
77  7       29      B
78  2       39      C
79  5       47      F

I recommend you to check out https://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x — Ricky Alexandersson, Jul 19 '17 at 07:35
Is the format of ColumnA consistent? are there always three items, or are columns always delimited by a space? @r — Xingzhou Liu, Jul 19 '17 at 07:37
@PatrickPangilinan *which* SQL Server version? 2016 already has a `STRING_SPLIT` method. In any case, you should split strings and cleanup the data *before* inserting it in the database. — Panagiotis Kanavos, Jul 19 '17 at 09:01

Xingzhou Liu · Answer 1 · 2017-07-19T08:42:35.030

0

(1) Examine solution to [this question] (T-SQL split string) - copy the split string function over. TSQL does not have a native function to do this if you are using anything older than 2016.

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(',', @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(',', @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END

see: T-SQL split string for original answer.

(2) Then do:

select sourcetbl.id, a.[Name] as columnValue , row_number() over (partition by BusinessEntityID order by (select 1)) as columnPosition
    from sourcetbl
    cross apply dbo.splitstring(joined_data) a;

you will get something like this:

Id | columnValue | columnPosition
1  | 5 | 1
1  | 47 | 2
1  | F | 3
2 | 2 | 1
2 | 5 | 2
2 | A | 3

Then pivot by ID and columnPosition to generate desired table.

edited Jul 19 '17 at 08:42

answered Jul 19 '17 at 08:08

Xingzhou Liu

1,507
8
12

can't understand this "This output can then populate columnB and C by joining on ID and columnPosition = 2 for B and columnPosition = 3 for C;" – Patrick Pangilinan Jul 19 '17 at 08:16
check the modifications above. I was able to get it to work using some sample data. – Xingzhou Liu Jul 19 '17 at 08:35
@XingzhouLiu this is probably the slowest way to split strings in T-SQL. The *second* one, by Aaron Bertrand is 10 times faster. Using XML is even faster. The fastest method is to write your own SQLCLR split function. Aaron Bertrand has written a series of articles that compares all string splitting techniques [here](https://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql) , [here](https://sqlperformance.com/2012/07/t-sql-queries/split-strings) and [here](https://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-follow-up) – Panagiotis Kanavos Jul 19 '17 at 09:10
Good to know. Makes sense thinking it through. Thanks for the informative links. – Xingzhou Liu Jul 19 '17 at 09:32

How to split string in a column then store the data by column in T-SQL?

1 Answers1