StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>As mentioned by others, your table design violates basic database design principles and if there is no way around it, you could normalize the table with little code (example below) and then join away with the other table. Here you go:</p> <p>Data:</p> <pre><code>CREATE TABLE data(RecNum varchar(20),Title varchar(10),Category varchar(15)) INSERT INTO data VALUES('wja-2012-000001','abcdef','4,6'), ('wja-2012-000002','qwerty','1,3,7'), ('wja-2012-000003','asdffg',null), ('wja-2012-000004','zxcvbb','2,7'), ('wja-2012-000005','ploiuh','3,4,12') </code></pre> <p>This function takes a comma separated string and returns a table:</p> <pre><code>CREATE FUNCTION listToTable (@list nvarchar(MAX)) RETURNS @tbl TABLE (number int NOT NULL) AS BEGIN DECLARE @pos int, @nextpos int, @valuelen int SELECT @pos = 0, @nextpos = 1 WHILE @nextpos > 0 BEGIN SELECT @nextpos = charindex(',', @list, @pos + 1) SELECT @valuelen = CASE WHEN @nextpos > 0 THEN @nextpos ELSE len(@list) + 1 END - @pos - 1 INSERT @tbl (number) VALUES (convert(int, substring(@list, @pos + 1, @valuelen))) SELECT @pos = @nextpos END RETURN END </code></pre> <p>Then, you can do something like this to "normalize" the table:</p> <pre><code>SELECT * FROM data m CROSS APPLY listToTable(m.Category) AS t where Category is not null </code></pre> <p>And then use the result of the above query to join with the "other" table. For example (i did not test this query):</p> <pre><code>select * from otherTable a join listToTable('1,3,5,6,8') b on a.Category = b.number join( SELECT * FROM data m CROSS APPLY listToTable(m.Category) AS t where Category is not null ) c on a.category = c.number </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload