String Split Function in SQL

Question

I am working with a large database that link client information using two columns: co_code and i_code. co_code includes the new ID's for our clients as we transfer data over from an older system. i_code is the prior client ID with information still relevant in the datasystem that I need to pull for a report I am creating.

The i_code column, however, sometimes has multiple ID's which fall under the same new co_code ID. For instance:

+---------+---------------------------+
| co_code |          i_code           |  
+---------+---------------------------|
|       1 |                       768 |  
|       2 |              134,4546,234 |  
|       3 |                      2354 |  
|       4 |                3454,65465 |  
|       5 | 432,76878,3543,43546,3435 |  
|       6 |                 535,65765 |  
|       7 |                      6345 |  
|       8 |                      1675 |  
|       9 |                       256 |  
|      10 |            3768,6585,4654 |  
+---------+---------------------------+

I have tried a series of different string split variations. My most recent is:

select co_code, i_Code from Prod_Detail where i_code in (select i_Code from string_split('i_code',','))

So far, I have not had any luck with splitting up the strings unless I do each individual row separately. As I am working with thousands of rows, this is not feasible within a small time frame.

Ideally, the output would look like the following:


+---------+--------+
| co_code | i_code |  
+---------+--------+
|       1 |    768 |  
|       2 |    134 |  
|       2 |   4546 |  
|       2 |    234 |  
|       3 |   2354 |  
|       4 |   3454 |  
|       4 |  65465 |  
|       5 |    432 |  
|       5 |  76878 |  
|       5 |   3543 |  
|       5 |  43546 |  
|       5 |   3435 |  
|       6 |    535 |  
|       6 |  65765 |  
|       7 |   6345 |  
|       8 |   1675 |  
|       9 |    256 |  
|      10 |   3768 |  
|      10 |   6585 |  
|      10 |   4654 |  
+---------+--------+

I am not aware of a `string_split()` function in MySQL. What database are you really using? — Gordon Linoff, May 23 '19 at 20:40
I think your answer is here https://stackoverflow.com/questions/51063730/split-one-row-into-multiple-rows-based-on-comma-separated-string-column — Carlos Alves Jorge, May 23 '19 at 20:47
The answer depends on what version of SQL Server. 2016+ would be a cross apply with string_split() — John Cappelletti, May 23 '19 at 20:57
Thank you @CarlosAlvesJorge, but the link does not support the SQL Server which I am using. — Codash, May 31 '19 at 20:02

under · Answer 1 · 2019-05-23T23:55:36.630

1

If you are using SQL Server 2016 or later you can use STRING_SPLIT function

See below example:

create table #code(co_code int, i_code varchar(max));
insert into #code(co_code, i_code)
values(1, '2,3,4'),(2, '3,4,5');

SELECT co_code, value as i_code_new
FROM #code
    CROSS APPLY STRING_SPLIT(i_code, ',');

If you are using an older version of SQL, you will have to write the split function yourself, or use one below:

CREATE FUNCTION [dbo].[udfSplitString]
(
    @List VARCHAR(MAX),
    @Delim VARCHAR(2)
)
RETURNS TABLE
AS
    RETURN ( SELECT [Value] FROM 
      ( 
        SELECT 
          [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
          CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
        FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
          FROM sys.all_objects) AS x
          WHERE Number <= LEN(@List)
          AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
      ) AS y
    );

You can then use it in the same way as with STRING_SPLIT:

SELECT co_code, value as i_code_new
FROM #code
    CROSS APPLY [udfSplitString](i_code, ',');

edited May 23 '19 at 23:55

answered May 23 '19 at 23:29

under

2,519
1
21
40

Thanks for your input @under. I have attempted your first example, and although the script runs, the i_code column still does not split the separate codes conglomerated in a single cell per the co_code. ``` ```IF OBJECT_ID('tempdb.. #code') IS NOT NULL DROP TABLE #code CREATE TABLE #code (co_code int , i_code varchar(max) ) INSERT INTO #code SELECT co_code , i_code FROM Prod_Detail as pd CROSS APPLY STRING_SPLIT(i_code,',') SELECT * FROM #code``` Any idea? I am still retrieving the following the above examples Apologies for way the code is showing! – Codash May 31 '19 at 20:04
Can you post what results you are getting and what you are expecting? – under Jun 01 '19 at 23:01
Hi, the results are exactly the same as above. My script doesn't seem to make a difference to the outcome at all. I was trying to paste it in here, but not enough characters. – Codash Jun 05 '19 at 15:05
You can update the question or send me a message. My script can be run as is. It produces (1,2),(1,3),(1,4),(2,3),(2,4),(2,5). What are you expecting to get instead? – under Jun 05 '19 at 22:19

String Split Function in SQL

1 Answers1