Comma separated (with curly brackets ) search in SQL

how to split comma separated values in sql query
using comma separated value parameter strings in sql in clauses
sql split comma delimited string into rows
sql split string by delimiter
reverse comma separated values in sql
sql convert comma separated string to list
sql split delimited string into columns
sql comma separated list

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

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.

Square bracket surrounded comma delimited list from a select , try this.. DECLARE @identify_entity NVARCHAR(MAX); SET @identify_entity = ''; SELECT @identify_entity = @identify_entity + ',[' +  Starting with SQL Server 2017, you can now make your query results appear as a list. This means you can have your result set appear as a comma-separated list, a space-separated list, or whatever separator you choose to use. While it’s true that you could achieve this same effect prior to SQL Server 2017, it was a bit fiddly.

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

You need one reference that is unique together

SQL Success, Curly brackets surround the whole table output, which includes the table name. on each row are returned between curly brackets as a comma-separated list of "​column name":value pairs. Fuzzy Searches — Method 00 l'lndecis au Precis se​. A comma seperated string F Note: This SQL also handles delimiter with more than one character. '+', -- CHR 91 and 93 are open/close square brackets

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/

Beginning Ruby: From Novice to Professional, 519–522 parsing, 519 reading and searching, 222–223 CSV (Comma-​Separated Value) files, saving data to, 223–224 curly braces {}, 36 239–243 DBI library, 242–244 MS SQL Server, 242 MySQL, 239–240 Oracle, 241 Postgre MySQL,  STRING_SPLIT (Transact-SQL) 11/28/2018; 4 minutes to read +9; In this article. APPLIES TO: SQL Server 2016 and later Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse . A table-valued function that splits a string into rows of substrings, based on a specified separator character. Compatibility level 130

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")

SQL Anywhere Studio 9 Developer's Guide, Be careful of the little differences, though; for example, curly braces in this of a very common construction in SQL, a comma-separated list: <user_name_list>  Here’s an example of retrieving data from a database, and combining two columns into one, separated by a comma: SELECT CONCAT_WS (', ', city.Name, country.Name) AS Location FROM city INNER JOIN country ON city.CountryCode = country.Code WHERE country.Code = 'THA';

Macromedia Coldfusion MX 7 Web Application Construction Kit, The STEM operator tells Verity to expand the search to include grammatical variations of the Table E.1 Verity Wildcards WILDCARD PURPOSE * Like the percent (%) wildcard in SQL, * stands in for any number of The curly brackets enable you to specify a number of possible word fragments, separated by commas. I need to match "xyz('Text1','Text2', Text3)" from the above string and need to get each word separated by a comma(,) within the bracket. Can anyone give the proper regex to do this? What I have tried: I have tried but cannot able to get that separated values properly

Comma Separated List, Within the authentication message structure, we find fields such as user name (​coded If the server accepts the authentication, it will send a list (comma-​separated Column level permissions can be managed using SQL Server Management name should be contained within square brackets as shown in Example 15.7. it looks like comma separated, always 4 values, and the values don't contain a comma themselves. if that's true, you can use the PARSENAME function to chop up the string easily, and get all 4 values;

Regex capture groups separated by comma, How do I extract all data in between two characters of a string in SQL ? Find answers to comma separate syslog regex from the expert community at values wrapped in square brackets and separated by a comma: match would be  I'm getting brackets { } in output . how can remove them in output. below is my package . OLEDB :- query the records in sql server. OLEDB : dump the records in sql server. below is how the output looks. EMPid emname {111} aaa {222} bbb. how to avoid these brackets {} in output. expected output : empid empname. 111 aaa. 222 bbb

Comments
  • Whenever you have two tables always go for the join operation first you need to join both the tables refer this w3schools.com/sql/sql_join.asp
  • Don't store data like that if you really want to use the individual elements in SQL expressions. See stackoverflow.com/a/3653574/20860
  • 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