CODEWITHSUNDEEP
sqlsybase
greasefirkins
greasefirkins

Reputation: 1

Retrieving data based on a delimited value

I'm new to sql and I'm stuck with the following problem where I have the following 2 tables:

Table A
|---------------------|-----------------------------------------------|
|          ID         |     Email                                     |
|---------------------|-----------------------------------------------|
|          12         | [email protected];[email protected];[email protected]     |
|---------------------|-----------------------------------------------|
|          13         | [email protected];[email protected]                 |
|---------------------|-----------------------------------------------|

Table B
    |---------------------|-----------------------------------------------|
    |          Name       |     Email                                     |
    |---------------------|-----------------------------------------------|
    |          Mark       | [email protected];                                |
    |---------------------|-----------------------------------------------|
    |          John       | [email protected]                                 |
    |---------------------|-----------------------------------------------|
    |          Alex       | [email protected]                               |
    |---------------------|-----------------------------------------------|
    |          Suds       | [email protected]                                 |
    |---------------------|-----------------------------------------------|
    |          Eric       | [email protected]                                 |
    |---------------------|-----------------------------------------------|

I now need to join the two tables but only show the Names of the users who have their email in Table A in which the the email column is delimited

|---------------------|
|          Name       |
|---------------------|
|          Mark       |
|---------------------|
|          John       |
|---------------------|
|          Alex       |
|---------------------|

Upvotes: 0

Views: 24

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270483

This is a horrible data model -- and should be fixed. Multiple values should not be stored in a single column.

That said, you can solve this with join:

select b.email
from a join
     b
     on ';' + a.email + ';' like '%;' + b.email + ';%';

Note the use of delimiters. This matches the first and last elements of the list.

Upvotes: 2

Related Questions