Splitting a delimeted string with 2 values .SQL Server 2008 . Can you help?

Question

I am passing a delimited string to a stored procedure that is composed by empId|ProductId + a comma as delimiter with the purpose of filling a link table. using SQL Server 2008

     EmployeeOrderLink  Table to be filled
     EmpId
     OrderId
     ProductId

Example of a possible key

     MyKeyIds="EmpId|ProductId,
     EG 2232|33,4555|111,43343|65 etc...

How do I loop through the string split it and insert into the table eg

   while MyKeyIds  ???
   Logic --PLEASE NOTE THAT EACH KEY IS COMPOSED BY 2 VALUES 
 AND SEPARATED BY THE COMMA.DELIMETER IS USED TO SEPARATE THE INNER VALUES OF THE KEY

         @myEmpID=--Get EmpId from split string
         @myProductId =Get productId from split string

         INSERT EmployeeOrderLinkend(EmpId,OrderId,ProductId)
         VALUES(@myEmpID,@OrderIdPassedAsParamInSP, @myProductId)
    END

Any suggestion on how to split the above key and extract the appropriate values? Thanks a lot

Answer 1

Here is an example of the split function in SQL. Another tutorial is this. Using it I think I would do the below to split at the comma. You would have to then add the code to split at the | also.

DECLARE @MyKeyIds NVARCHAR(40)
DECLARE @IDset NVARCHAR(40)
DECLARE @Pos INT
DECLARE @NextPos INT
DECLARE @Delimiter NVARCHAR(40)

SET @Delimiter = ','
Set @MyKeyIds='2232|33,4555|111,43343|65'+ @Delimiter 
SET @Pos = charindex(@Delimiter, @MyKeyIds)

WHILE (@pos <> 0)
BEGIN
    SET @IDset = substring(@MyKeyIds,1,@Pos - 1)
    SELECT @IDset -- Show Results
    SET @MyKeyIds = substring(@MyKeyIds,@Pos+1,len(@MyKeyIds))
    SET @Pos = charindex(@Delimiter,@MyKeyIds)
END 

Answer 2

Another SQL split function. Basically, a split() function will create a table from your delimited list. You can then join against that table as if it were a "real" table.

Answer 3

As you're on SQL Server 2008 you can use Table-Valued Parameters to avoid this issue entirely.

If you are calling the stored proc from ADO.NET this link might be useful.

Answer 4

This code will parse the string by comma and then split the result based on the position of the pipe:

SET NOCOUNT ON

DECLARE 
  @keyPair VARCHAR(1000),
  @myEmpID VARCHAR(1000),
  @myProductID VARCHAR(1000)

DECLARE @myKeyIDs VARCHAR(1000)
SET @myKeyIDs = '2232|33,4555|111,43343|65'

DECLARE
  @len INT,
  @pos INT,
  @found INT

SELECT
  @len = LEN(@myKeyIDs),
  @pos = 1

SET @myKeyIDs = @myKeyIDs + ','

/* Find the first instance of a comma */
SET @found = CHARINDEX(',', @myKeyIDs, @pos)

WHILE @found > 0
BEGIN  

  /* The key pair starts at the @pos position and goes */
  /* to the @found position minus the @pos position   */
  SET @keyPair= SUBSTRING(@myKeyIDs, @pos, @found - (@pos))

  /* Double-check that pipe exists to avoid failure */
  /* If no pipe exists, assume value is myEmpID     */
  IF CHARINDEX('|',@keyPair) = 0
  BEGIN
    SET @myEmpID = NULLIF(@keyPair, '')
    SET @myProductID = NULL
  END
  ELSE
  BEGIN
    /* myEmpID is everything left of the pipe */
    /* myProductID is everything on the right */
    SET @myEmpID = NULLIF(SUBSTRING(@keyPair, 1, 
        CHARINDEX('|', @keyPair) - 1), '')
    SET @myProductID = NULLIF(SUBSTRING(@keyPair, 
        CHARINDEX('|', @keyPair) + 1, LEN(@keyPair) - 1), '')
  END

  /*
  INSERT EmployeeOrderLinkend(EmpId,OrderId,ProductId) 
  VALUES(@myEmpID,@OrderIdPassedAsParamInSP, @myProductId) 
  */
  SELECT @myEmpID AS myEmpID, @myProductID AS myProductID

  /* Move to the next position and search again */
  SET @pos = @found + 1
  SET @found = CHARINDEX(',', @myKeyIDs, @pos)

END

One of the problems with string parsing is trying to handle all the edge cases. You have to prepare for things like missing commas, missing pipes, too many pipes, confirming your values are numeric, and so on. We have also migrated to using Table-Valued Parameters when possible...