Menu
I have a MySQL field with a reference to another table where ids are saved as comma seperated list, eg:
12,13,14,16 which stand for values in another table. I know this is very bad and wrong, but this comes from above and I cant do anything about that. The problem now is that i want to search in that field with a query like this:
SELECT ... WHERE field LIKE '%1%' The Problem now is obviously that almost all entries can be found with this example Query, because the most common IDs are in Range 10-20. My Idea is to search for %,1,% instead, but this does not work for the first and last id in the field. Ist there something like an internal replace or how do i fix this the best way?
427
To check if value exists in a comma separated list, you can use FIND_IN_SET() function. Now you can insert some records in the table using insert command. Display all records from the table using select statement.
Finding duplicate emails By using Sub-query with EXISTS: In a correlated subquery, the inner query is executed for each record in the outer query. So one email is compared to the rest of the email in the same table using a correlated subquery and EXISTS clause in SQL as shown below.
In order to fetch the comma separated (delimited) values from the Stored Procedure, you need to make use of a variable with data type and size same as the Output parameter and pass it as Output parameter using OUTPUT keyword.
You need the FIND_IN_SET function:
SELECT ... WHERE FIND_IN_SET('1', field) Be aware that plain FIND_IN_SET is case-insensitive,
i.e. FIND_IN_SET('b3','a1,a2,B3,b3') and FIND_IN_SET('B3','a1,a2,B3,b3') both return 3.
To be case sensitive, add 'binary' modifier to the 1st argument, e.g. FIND_IN_SET (binary 'b3', 'a1,a2,B3,b3') returns 4.
24
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
