SQL Joining with Imperfect Match

Question

Is it possible to match the following combination of such keys in SQL?

The key values like an array and delimiter = '/'.

Key      
-----
A/B/C
A
B
C
A/B
A/C
B/C

Answer 1

Assuming here your keys are single letters as in your example, you could use LIKE:

SELECT * FROM table WHERE key LIKE '%A%'

Would give you all the values where "key" contains "A".

Answer 2

SELECT  *
FROM    mytable
WHERE   `key` REGEXP '^([ABC]/)*[ABC]?$'

This will match anything from above, but will not match if there are other letters (like D/B/C or AA/BB/CC)

Answer 3

Thanks a lot for your answer. But this SQL cannot match keys 'A/B/C' and 'A/C' by using WHERE Key LIKE '%' || Key || '%'.

Answer 4

You will be better off using REGEXP

SELECT * FROM table WHERE key REGEXP '[[:<:]]A[[:>:]]'

[[:<:]] and [[:>:]] mark a word boundry, this will stop 'A' matching AB,AC,AD I'm not sure if they are mysql specific

Answer 5

What database?

• If dealing with Oracle, I suggest using INSTR
• For SQL Server: CHARINDEX
• mySQL also uses INSTR

Use any of those to test for a value greater than zero.

SELECT t.*
  FROM TABLE t
 WHERE INSTR(t.column, expectedValue) > 0

Answer 6

Thanks ALL. 'A/C' is not INSTR of 'A/B/C'. Oracle solution is preferred.

Answer 7

You haven't stated your problem very clearly. I'm taking it that this list of keys conceptually rep ordered sets, and you want to find all possible subset / superset combinations (e.g. I think you want 'A/C' to "match" 'A/B/C').

This seems to work but I'd be hard pressed to prove that the logic is right:

SELECT a.key subset, b.key superset
  FROM key_list a, key_list b
  WHERE '/' || REPLACE( b.key, '/', '//') || '/'
      LIKE  '/' || REPLACE( a.key, '/', '/%/' ) || '/'
     OR b.key LIKE '%' || a.key || '%'
  ORDER BY length(a.key), a.key, length(b.key),b.key

Answer 8

My first thought to you is that you need to redesign. You should not store data that way. You should have a related table instead. Then you can do ordinary joins to get what you want. Rule 1 of database design is to store only one piece of information per field. If you are finding you need to break this down into smaller chunks than you are storing, you are storing incorrectly.

Some of the proposed solutions will work (depending on what you are really asking which is not clear) but most if not all of them will be slow as they rely on syntax which will not alow you to use indexes. This is one major reason why a redesign is indicated. You do not want a system where the indexes can't be used.