Splitting a column in SQL, then looking up each one in another table

I'm not sure if this is possible in SQL, and if not I'll do it programatically - but it'd be great if there was a way.

Basically, I have a column in a table which gets populated with ID's from another table, seperated by commas - so a field might look like this:

3,4,9

These link to another table, where 开发者_如何学Gothe numbers in the field above are the primary key of a row. In the row is a description, which I want to display to the user rather than numbers.

So basically, rather than display 3,4,9 to the user, I want to look up their relevant descriptions in another table. Is this possible?

You can write a table-valued function that does the splitting and then join that result with your lookup-table.

Chris,

There IS a way to do this in T-SQL and it's actually pretty easy. First, you need to create a function like the following... (brand new method using zero based "Tally" cte, it's nasty fast because it doesn't concatenate any delimiters).

 CREATE FUNCTION dbo.DelimitedSplit8KNEW
--===== Created by Jeff Moden (Prototype: Testing Still in Progress)
--===== Define I/O parameters
        (
        @pString    VARCHAR(8000),
        @pDelimiter CHAR(1)
        )
RETURNS TABLE
   WITH SCHEMABINDING
     AS
 RETURN
WITH 
      E1(N) AS (
                SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
               ),                          --10
      E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100
      E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000
cteTally(N) AS (
                SELECT 0 UNION ALL
                SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E4
               )
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY t.N),
        ItemValue  = SUBSTRING(@pString,t.N+1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,t.N+1),0),DATALENGTH(@pString)+1)-t.N-1)
   FROM cteTally t
  WHERE t.N BETWEEN 0 AND DATALENGTH(@pString)
    AND (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)
;
GO

Now, to see how it's used... let's say you have a table with a unique column and your CSV column as follows (this builds the data, as well). All we have to do is CROSS APPLY the original data with the function and the data is magically split ready to be joined with another table:

--===== This just builds some test data 
     -- and is not a part of the solution
 SELECT *
   INTO #TestTable
   FROM (
         SELECT 1,'3,4,9'   UNION ALL
         SELECT 2,'3,2,100' UNION ALL
         SELECT 3,'14,35,8,21,27,12'
        ) d (RowNum,CsvValue)
;
--===== Split the data out giving the unique RowNum
     -- from the original data, the element position,
     -- the the value of the split element.  You can
     -- join this SELECT with a table to get the other
     -- values.
 SELECT data.RowNum, split.ItemNumber, split.ItemValue
   FROM #TestTable data
  CROSS APPLY dbo.DelimitedSplit8KNEW(data.CsvValue,',') split
;

Here's the output...

RowNum      ItemNumber           ItemValue
----------- -------------------- ---------
1           1                    3
1           2                    4
1           3                    9
2           1                    3
2           2                    2
2           3                    100
3           1                    14
3           2                    35
3           3                    8
3           4                    21
3           5                    27
3           6                    12

(12 row(s) affected)

Would you be interested in changing your schema? In general, it's a horrible practice to store multiple pieces of data within a single field. A One to many relationship seems more suitable.

You might want to look at this:

http://www.sommarskog.se/arrays-in-sql.html

And use that to do a subquery with a IN clause.