Comma separated (with curly brackets ) search in SQL

Question

I have a SQL Table like this:

Table1:

| SomeID1        | OtherID1    | Data1
+----------------+-------------+-------------------
| abcdef-.....   | cdef123-... | {18,20,22}
| abcdef-.....   | 4554a24-... | {17,19}
| 987654-.....   | 12324a2-... | {13,19,20}

And another table with:

Table 2:

| SomeID2        | OtherID2    | Data2
+----------------+-------------+-------------------
| abcdef-.....   | cdef123-... | 13
| abcdef-.....   | 4554a24-... | 14
| 987654-.....   | 12324a2-... | 15
| abcdef-.....   | 4554a24-... | 16
| 987654-.....   | 12324a2-... | 17

Is it possible to gather one Data1 value from table1 and search in table2 like:

select * from table2 where Data2 in ('18','20','22')

Im looking for something like this:

select * from table2 where Data2 in (select Data1 from table1 where SomeID1='abcdef')

PD: I did not make the table

Whenever you have two tables always go for the join operation first you need to join both the tables refer this https://www.w3schools.com/sql/sql_join.asp — Sachin Yadav, Sep 27 '19 at 21:46
Don't store data like that if you really want to use the individual elements in SQL expressions. See https://stackoverflow.com/a/3653574/20860 — Bill Karwin, Sep 27 '19 at 21:47

Marcel Kraan · Answer 1 · 2019-09-27T21:58:22.437

0

SELECT SomeID1, OtherID1, Data1 FROM Table1,Table2 WHERE SomeID1 = SomeID2 AND ....

You need one reference that is unique together

edited Sep 27 '19 at 21:58

answered Sep 27 '19 at 21:54

Marcel Kraan

91
1
9

score 0 · Answer 2 · answered Sep 27 '19 at 22:00

SOLUTION #1 (programming language)

This can be done with any programming language

1: Prepare both statements

2: Execute your first query Ex. SELECT Data1 FROM table1

3: Explode Data1 field by commas, store the exploded var in an array (trim curly braces first)

4: Loop through your array and execute your second query Ex. SELECT * FROM table2 WHERE Data2 = array[index++]

5: Get your results whenever there's a match

SOLUTION #2 (PL/SQL)

Using only PL/SQL a cursor can be helpful for what you're trying to accomplish

http://www.sqlservertutorial.net/sql-server-stored-procedures/sql-server-cursor/

score 0 · Accepted Answer · answered Sep 27 '19 at 22:16

0

If SomeID1 is unique you can do this:

select * from table2 
where (select replace(replace(Data1, '{', ','), '}', ',') from table1 where SomeID1=?) 
like concat('%,', Data2, ',%')

This works for SQL Server and MySql and you can adjust it to work for any database.
See the demo.

answered Sep 27 '19 at 22:16

forpas

160,666
10
38
76

yes! it works for me, and i forgot to mention that SomeID1 is unique. Now im only worried about how optimized is this query since table2 is a very large table – David Ricardo Menacho Vadillo Sep 28 '19 at 00:19

exxbrain · Answer 4 · 2019-09-27T22:28:24.033

Is it possible for you to normalize data with To-Many relationship by introducing one more table? So you will have Table 3:

| table1SomeID1  | Data1
+----------------+-------------
| abcdef-.....   | 18
| abcdef-.....   | 20
| abcdef-.....   | 22
| abcdef-.....   | 17
| abcdef-.....   | 19
| 987654-.....   | 13
| 987654-.....   | 19
| 987654-.....   | 20

You will be able to make queries like:

select * from table2 where data2 in (select Data1 from table3 where table1SomeID = "abcdef")

Comma separated (with curly brackets ) search in SQL

4 Answers4