1

SQL Issue

For an stored procedure input such as:

  • @CustomerID = '1,2,3,4,'
  • @Address = 'Oregon'
  • @City = 'Portland'

. I need to insert values into separate rows such as below: 

    customerID | address city
 --------------+------------------
    1          |  Oregon Portland
    2          |  Oregon Portland
    3          |  Oregon Portland
    4          |  Oregon Portland

I need help regarding how to insert CSV values in single parameter to separate rows.

ɐlǝx
  • 1,384
  • 2
  • 17
  • 22
Jhjry smth
  • 197
  • 1
  • 1
  • 12
  • 1
    check this answer http://stackoverflow.com/questions/10914576/t-sql-split-string – Karthik Ganesan Feb 23 '17 at 16:34
  • 1
    What version of SQL Server are you using? Does your `@CustomerID` parameter have to be a delimited string, or could you use a [table valued parameter](https://msdn.microsoft.com/en-us/library/bb510489(v=sql.100).aspx)? – GarethD Feb 23 '17 at 16:42

6 Answers6

6

Using a CSV Splitter function by Jeff Moden:

declare @CustomerId varchar(8000) = '1,2,3,4,'
      , @Address    varchar(64)   = 'Oregon' 
      , @City       varchar(64)   = 'Portland'

select 
    CustomerId = d.Item
  , Address = @Address
  , City = @City
from [dbo].[delimitedsplit8K](@CustomerId,',') as d
where d.Item <> ''

test setup: http://rextester.com/COUJVK17338

returns: 

+------------+---------+----------+
| CustomerId | Address |   City   |
+------------+---------+----------+
|          1 | Oregon  | Portland |
|          2 | Oregon  | Portland |
|          3 | Oregon  | Portland |
|          4 | Oregon  | Portland |
+------------+---------+----------+

splitting strings reference:

SqlZim
  • 37,248
  • 6
  • 41
  • 59
  • I think you have missed the most pertinent piece by Aaron Bertrand on this subject - [Splitting Strings : Now with less T-SQL](https://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql) - where actually the best practice is to avoid splitting all together and pass a table valued function. That being said still the best answer so gets my vote. – GarethD Feb 23 '17 at 16:44
  • @GarethD I haven't missed it, but if I posted all of the links about string splitting by Aaron Bertrand that I thought were pertinent, the list would be a little overwhelming. For those interested, they will find many links within those articles to other articles. Here are two more. - [STRING_SPLIT() in SQL Server 2016 : Follow-Up #1 - Aaron Bertrand](https://sqlperformance.com/2016/04/sql-server-2016/string-split-follow-up-1) - [STRING_SPLIT() in SQL Server 2016 : Follow-Up #2 - Aaron Bertrand](https://sqlperformance.com/2016/04/sql-server-2016/string-split-follow-up-2) – SqlZim Feb 23 '17 at 16:47
  • Thank you for the help and references – Jhjry smth Feb 23 '17 at 17:20
1

One method is a recursive CTE:

with c as (
      select left(@CustomerId, charindex(',', @CustomerId) - 1) as customerid,
             substring(@CustomerId, charindex(',', @CustomerId) + 1, 8000) as rest
      union all
      select left(rest, charindex(',', rest) - 1),
             substring(rest, charindex(',', rest) + 1, 8000)
      from c
      where rest like '%,%'
     )
insert into t(customerid, Address, City)
    select customerid, @Address, @City
    from c
    where customerid <> '';
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you, this seems to be working but it works only for single digit values for Ids such as 1,2,3,4 but not for 11,12,13. How can I set the length selection dynamically ? – Jhjry smth Feb 23 '17 at 17:29
  • @Jhjrysmth . . . I missed a reference to `@CustomerId` in the recursive part of the CTE. I changed it to `rest`. – Gordon Linoff Feb 24 '17 at 01:56
0

use a while loop 

WHILE len(@CustomerID) >1
BEGIN
insert into MyTable (customerID, Address, City)
values (left(@CustomerID, charindex(',',@CustomerID)), @Address, @City)

@CustomerID = substring(@CustomerID, 
                        charindex(',',@CustomerID)+1,
                        len(@CustomerID)-charindex(',',@CustomerID))

END
JohnHC
  • 10,935
  • 1
  • 24
  • 40
0

A fun way using xQuery

declare @CustomerXML xml = '<i>' + replace(@CustomerID, ',', '</i><i>') + '</i>'

select 
    i.value('.', 'int') as customerID,
    @Address as address,
    @City as city
from @CustomerXML.nodes('for $i in /i where $i != "" return $i') as T(i)
Aducci
  • 26,101
  • 8
  • 63
  • 67
0

I use a custom made Table valued Split function that splits delimited strings on custom specified delimiters and returns the results in a table variable. 

CREATE FUNCTION dbo.[Split]
      (
            @List NVARCHAR(2000),
            @SplitOn NVARCHAR(5)
      ) 
      RETURNS @RtnValue TABLE
      (
            Id INT IDENTITY(1,1),
            Value NVARCHAR(100)
      )
      AS 
      BEGIN
            WHILE (CHARINDEX(@SplitOn,@List)>0)
            BEGIN

                  INSERT INTO @RtnValue (value)
                  SELECT
                        Value = LTRIM(RTRIM(SUBSTRING(@List,1,CHARINDEX(@SplitOn,@List)-1)))

                  SET @List = SUBSTRING(@List,CHARINDEX(@SplitOn,@List)+LEN(@SplitOn),LEN(@List))
            END

            INSERT INTO @RtnValue (Value)
            SELECT Value = LTRIM(RTRIM(@List))
            RETURN
      END;
      GO

     SELECT * from dbo.Split('1,2,3,1',',')
     GO

Sample Link: http://rextester.com/FXX54250

Kashyap MNVL
  • 603
  • 4
  • 13
0

If using SQL Server 2016 or later (SQL Server 13.0), you can use the new STRING_SPLIT function.

declare @CustomerID varchar(50) = '1,2,3,4,',
    @Address varchar(50) = 'Oregon',
    @City varchar(50) = 'Portland'    

SELECT value as CustomerID, @Address, @City
FROM STRING_SPLIT(@CustomerID, ',')  
WHERE RTRIM(value) <> '';

Output:

CustomerID  Address City
1           Oregon  Portland
2           Oregon  Portland
3           Oregon  Portland
4           Oregon  Portland
SteveD
  • 819
  • 7
  • 13