Menu
Is it possible to combine the CASE statement and the LIKE operator in a MySQL SELECT statement?
For Example, I am trying to query a database that stores data in a single column in either one of two formats (this is awful and hurts my head, but I cant change the data so it is what it is.). So sometimes the column numbers would have data like "6901xxxxxxxx" and sometimes it would have data like "91xxxxxxxxxxx".
What I would like to do is query the data like so -
SELECT CASE digits WHEN LIKE "6901%" THEN substr(digits,4) WHEN LIKE "91%" THEN substr(digits,2) END as "digits", FROM raw This obviously doesn't work but Im hoping its possible.
200
LIKE performs case-insensitive substring matches if the collation for the expression and pattern is case-insensitive. For case-sensitive matches, declare either argument to use a binary collation using COLLATE , or coerce either of them to a BINARY string using CAST .
When searching for partial strings in MySQL with LIKE you will match case-insensitive by default*. If you want to match case-sensitive, you can cast the value as binary and then do a byte-by-byte comparision vs. a character-by-character comparision. The only thing you need to add to your query is BINARY .
By default, it's case-insensitive, and like with MySQL, you can change this behavior by modifying the collation setting.
Definition and Usage. The CASE statement goes through conditions and return a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it will return the value in the ELSE clause.
Using the second form of CASE should work:
SELECT CASE WHEN digits LIKE '6901%' THEN substr(digits,4) WHEN digits LIKE '91%' THEN substr(digits,2) END as digits FROM raw Furthermore, you have a stray comma at the end of your SELECT.
50
Try
SELECT CASE true WHEN digits LIKE "6901%" THEN substr(digits,4) WHEN digits LIKE "91%" THEN substr(digits,2) END as "digits", FROM raw 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
