I want to find a word in a string with SQL. I currently use:
SELECT * FROM dreams
WHERE
title
LIKE '%lo%'
But I want also to find other spellings like "Lo" or "LO" and so on..
Any Ideas ?
Asked
Active
Viewed 246 times
0
-
2What RDBMS are you using? – Mureinik Jul 18 '15 at 14:08
-
I only know mySQL, and in that case you would need to configure a new database to be case sensitive. There is a also a REGEXP implemented that I'd bet is fairly universal. – wbg Jul 18 '15 at 14:11
-
Not necessarily - using the OR operator in the MySQL query would suffice. – Aetiranos Jul 18 '15 at 14:13
3 Answers
4
Convert it to upper case before comparing
SELECT * FROM dreams
WHERE
upper(title)
LIKE '%LO%'
ramana_k
- 1,933
- 2
- 10
- 14
-
I'm not convinced his query is case-sensitive... mine normally return case-insensitive results... need the server to know. – CLaFarge Jul 18 '15 at 14:23
-
0
SELECT * FROM dreams WHERE title LIKE '%lo%'
union
SELECT * FROM dreams WHERE title LIKE '%LO%'
union
SELECT * FROM dreams WHERE title LIKE '%Lo%';
Aetiranos
- 329
- 1
- 6
-
If case-insensitive, this will return the same results for all three, giving 3*duplicate results... maybe SELECT * FROM dreams WHERE title LIKE '%lo%' OR title LIKE '%LO%' OR title LIKE '%Lo%'; – CLaFarge Jul 18 '15 at 14:26
-
@CLaFarge: no it will not return duplicates because `UNION` will remove them. `UNION ALL` would keep duplicates. But I do agree that this is not the most efficient way to do this. Ramana's solution is the right one. – Jul 18 '15 at 16:20
-
Nice to know... I'd still use one query, given the opportunity. In fact, I think I'd use something like: SELECT * FROM dreams WHERE LOWER(title) LIKE '%lo%'; – CLaFarge Jul 18 '15 at 18:03
0
We have no idea what version of SQL, but here's a very possibly related post on SOundex, and another on Full Text Searching, a page on CONTAINS.
The more info you provide, the better able we are to help.
I hope that SOUNDEX, or Full Text Searching, or CONTAINS, alternatively, are useful to you.
