Teradata SQL code to join against a string

user_9416260 0
Viewed 140

I have a table A that has the below values

+----+----------+-----------------------+
| ID |   Date   |         Name          |
+----+----------+-----------------------+
|  1 | 1/4/2019 | Kara,Sara,John        |
|  2 | 3/2/2018 | Sara                  |
|  3 | 4/3/2019 | Lynn,John,Chris,Agnes |
|  4 | 2/1/2020 | Phillip, Anton        |
|  5 | 5/1/2020 | Quinn                 |
|  6 | 7/6/2020 | Idie,John             |
+----+----------+-----------------------+

And a table B that has the below values

+-------+
| Name  |
+-------+
| John  |
| Sara  |
| Chris |
+-------+

I would like the output to be as below:

+----+----------+-----------------------+--------+-----------------+
| ID |   Date   |         Name          | B.Name | Exists in List? |
+----+----------+-----------------------+--------+-----------------+
|  1 | 1/4/2019 | Kara,Sara,John        | Sara   | Yes             |
|  1 | 1/4/2019 | Kara, Sara, John      | John   | Yes             |
|  2 | 3/2/2018 | Sara                  | Sara   | Yes             |
|  3 | 4/3/2019 | Lynn,John,Chris,Agnes | John   | Yes             |
|  3 | 4/3/2019 | Lynn,John,Chris,Agens | Chris  | Yes             |
|  4 | 2/1/2020 | Phillip, Anton        |        | No              |
|  5 | 5/1/2020 | Quinn                 |        | No              |
|  6 | 7/6/2020 | Idie,John             | John   | Yes             |
+----+----------+-----------------------+--------+-----------------+

I tried using CONTAINS but looks like teradata sql does not accept it. Tried CSVLD to convert text to column.However since there is no fixed number of commas that the string can accept, I cannot use CSVLD function if I do not know precisely how many columns I need to re-create from the text beforehand. Wondering if there is any alternative to join a column against a string of values? Appreciate your kind input.

teradata sql
1 Answers

You should really fix your data model -- storing multiple values in a string is a bad, bad data design. SQL has a great way of storing lists -- it is called a table.

Assuming you are stuck with someone else's really, really bad data model, you can use a left join:

select a.*, b.name,
       (case when b.name is not null then 'Yes' else 'No' end) as in_list
from a left join
     b
     on ',' || a.name || ',' like '%,' || b.name || ',%';
user_11440350
Related