SQL - select where row items match other row items
I have a table field, name it pattern
containing a comma separated list of values. For example: '10, 20,30,40,50 '
I need to select * from a specified table where at least one item from another similar row also appears in this field.
For example, say I have the string '10, 50.70 'A record should be selected whose field pattern
is '10, 20,30,50,70' because 10 and 70 are present in '10, 50,70 '. `
Is there a way to do that, but the set of OR, where I check to see if pattern
the LIKE '% 10%' or pattern
the LIKE% to 50% or pattern
the LIKE% to 70%?
source to share
You can use FIND_IN_SET()
alternatively as shown below, although, as suggested in the comment, consider normalizing your table.
SELECT * FROM table_name
WHERE FIND_IN_SET('10','10,20,30,50,70')
OR FIND_IN_SET('50','10,20,30,50,70')
OR FIND_IN_SET('70','10,20,30,50,70') ;
source to share
As mentioned by Patrick Hoffman, the best solution is to normalize the database.
If you really have to stick to a table like this, then there are several solutions.
You can split the comma separated values and combine the results based on that. Not that the cross-joined tables simply generate a range of numbers (in this case, 0 to 99), but it must be equal to or greater than the maximum number of separable values. It will also be slow since no indexes can be used. In addition, a large number of records are generated in memory to perform these calculations.
Something like that: -
SELECT DISTINCT sub0.id, sub1.id
FROM
(
SELECT DISTINCT id, SUBSTRING_INDEX(SUBSTRING_INDEX(pattern, ',', units.i + tens.i * 10), ',' -1) AS pattern_id
FROM some_table
CROSS JOIN (SELECT 1 AS i UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) units
CROSS JOIN (SELECT 1 AS i UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) tens
) sub0
INNER JOIN
(
SELECT DISTINCT id, SUBSTRING_INDEX(SUBSTRING_INDEX(pattern, ',', units.i + tens.i * 10), ',' -1) AS pattern_id
FROM some_table
CROSS JOIN (SELECT 1 AS i UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) units
CROSS JOIN (SELECT 1 AS i UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) tens
) sub1
ON sub0.pattern_id = sub1.pattern_id
AND sub0.id != sub1.id
If the comma separated value is a key to another table, you can make a pair of connections based on FIND_IN_SET.
SELECT DISTINCT a.*, b.*
FROM some_table a
INNER JOIN some_patterns b
ON FIND_IN_SET(b.id, a.patterns)
INNER JOIN some_table c
ON FIND_IN_SET(b.id, c.patterns)
In reality, it would be best to normalize your database, perhaps using SQL based on these solutions to help you extract the data into your new normalized format.
source to share
I inherited this method of storing small lists of integers and wrote an included function to rotate CSV integers into a column.
CREATE function [dbo].[udf_IntegerColumnFromCSV] ( @DelimStr nvarchar(max) )
returns @ListOfInts table ( Value int )
AS
BEGIN
set @DelimStr = @DelimStr + ',' -- add one more to the end
declare @i int -- the "start" of the next value to be read
declare @j int -- the location of the next comma
declare @le int -- line end
set @i = 1
set @j = 1
set @le = len(@DelimStr)
while ( @j < @le ) begin
set @j = charindex( ',', @DelimStr, @i ) -- find the end of the next row
insert into @ListOfInts (Value) values ( cast(substring( @DelimStr, @i, @j-@i ) as integer) )
set @i = @j + 1
end
return
end
In a situation where you have a list of values stored in CSV:
select ID, ValueList from TableX
ID: ValueList
20: 38, 39, 40, 41, 42, 44, 61.62.63.64.65.66, 70.71.72
21:12
22: 61.62.63.64.65.66
Use a function to rotate values into a column:
select ID, vl. * from TableX cross apply dbo.udf_IntegerColumnFromCSV (ValueList) vl
20:38
20: 39
20: 40
20: 41
20: 42
20: 44
20: 61
20: 62
20: 63
20: 64
20: 65
20: 66
20: 70
20: 71
20: 72
21:12
22: 61
22: 62
22: 63
22: 64
22: 65
22: 66
source to share