How can I run a query on IDs in a string?

Question

I have a table A with this column:

  IDS(VARCHAR)
  1|56|23

I need to run this query:

  select TEST from TEXTS where ID in ( select IDS from A where A.ID = xxx )

TEXTS.ID is an INTEGER. How can I split the string A.IDS into several ints for the join?

Must work on MySQL and Oracle. SQL99 preferred.

Answer 1

First of all, you should not store data like this in a column. You should split that out into a separate table, then you would have a normal join, and not this problem.

Having said that, what you have to do is the following:

1. Convert the number to a string
2. Pad it with the | (your separator) character, before it, and after it (I'll tell you why below)
3. Pad the text you're looking in with the same separator, before and after
4. Do a LIKE on it

This will run slow!

Here's the SQL that does what you want (assuming all the operators and functions work in your SQL dialect, you don't say what kind of database engine this is):

SELECT
    TEXT   -- assuming this was misspelt?
FROM
    TEXTS  -- and this as well?
    JOIN A ON
        '|' + A.IDS + '|' LIKE '%|' + CONVERT(TEXTS.ID) + '|%'

The reason why you need to pad the two with the separator before and after is this: what if you're looking for the number 5? You need to ensure it wouldn't accidentally fit the 56 number, just because it contained the digit.

Basically, we will do this:

... '|1|56|23|' LIKE '%|56|%'

If there is ever only going to be 1 row in A, it might run faster if you do this (but I am not sure, you would need to measure it):

SELECT
    TEXT   -- assuming this was misspelt?
FROM
    TEXTS  -- and this as well?
WHERE
    (SELECT '|' + IDS + '|' FROM A) LIKE '%|' + CONVERT(TEXTS.ID) + '|%'

If there are many rows in your TEXTS table, it will be worth the effort to add code to generate the appropriate SQL by first retrieving the values from the A table, construct an appropriate SQL with IN and use that instead:

SELECT
    TEXT   -- assuming this was misspelt?
FROM
    TEXTS  -- and this as well?
WHERE
    ID IN (1, 56, 23)

This will run much faster since now it can use an index on this query.

If you had A.ID as a column, and the values as separate rows, here's how you would do the query:

SELECT
    TEXT   -- assuming this was misspelt?
FROM
    TEXTS  -- and this as well?
    INNER JOIN A ON TEXTS.ID = A.ID

This will run slightly slower than the previous one, but in the previous one you have overhead in having to first retrieve A.IDS, build the query, and risk producing a new execution plan that has to be compiled.