separate comma strings in parameter and save in table SQL

Question

I have a parameter with comma in it. Like @parameter = 'abc,xyz,fgh' I want to separate it like:

column1     column2
1           abc
1           xyz
1           fgh

I have a trigger which is receiving a parameter named @PackageCode with comma values. Now I want to separate its value and save it in table.

Below is the SQLtrigger code I've tried so far.

CREATE TRIGGER CustomerPackageSplit ON BM_CustomerInfo
FOR INSERT
AS
    declare @CustNIC int;
    declare @CustID varchar(100);
    declare @CustName decimal(10,2);
    declare @PackageCode varchar(100);

SELECT @CustNIC = i.CustNIC from inserted i;
SELECT @CustID = i.CustID from inserted i;
SELECT @CustName = i.CustName from inserted i;
SELECT @PackageCode = i.Package from inserted i;


INSERT INTO BM_CustPackage (CustNIC,CustID,CustName,PackageCode)
VALUES (@CustNIC,@CustID,@CustName,@PackageCode)

GO

You should really do the split before inserting the data into the table. The trigger also has a serious fault, inserted (and deleted) can contain more than one row. — James Z, Mar 04 '17 at 20:26

score 1 · Answer 1 · edited May 23 '17 at 12:17

In SQL Server 2016 you can use string_split function (more info here).

Here is a simple query that splits your comma separated values:

declare @parameter   varchar(100)

set @parameter = 'abc,xyz,fgh'

select 1 as column1, [value] as column2 
from string_split(@parameter, ',')

The output of this command is:

╔═════════╦═════════╗
║ column1 ║ column2 ║
╠═════════╬═════════╣
║       1 ║ abc     ║
║       1 ║ xyz     ║
║       1 ║ fgh     ║
╚═════════╩═════════╝

For older SQL Server version you can find many custom implementations of split functions, for example here, here or here.

separate comma strings in parameter and save in table SQL

1 Answers1