CODEWITHSUNDEEP
sqlteradata
Vinay
Vinay

Reputation: 65

Teradata SQL code to join against a string

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.

Upvotes: 2

Views: 405

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270643

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 || ',%';

Upvotes: 1

Related Questions