0

Field X in table may contain special characters e.g hello!World and I would like to know if there is a way to match that with HelloWorld (Ignore case and special characters).
SELECT * FROM table WHERE X='Helloworld'

Thanos Markou
  • 2,587
  • 3
  • 25
  • 32
Stefanos Chrs
  • 2,228
  • 3
  • 19
  • 46

4 Answers4

1

http://sqlfiddle.com/#!9/2afa1/1

if you need exaclty match of string:

SELECT * 
FROM table1
WHERE x REGEXP '^hello[[:punct:],[:space:]]world$';

And if hello world could be a part of larger string:

SELECT * 
FROM table1
WHERE x REGEXP 'hello[[:punct:],[:space:]]world';
Alex
  • 16,739
  • 1
  • 28
  • 51
  • The thing is I don't know where the Special Character will be in the string, @fancyPants answer did the trick – Stefanos Chrs Apr 29 '15 at 19:11
  • that is up to you, you should clarify better next time when ask. My answer is exactly what you was asking for. – Alex Apr 29 '15 at 19:14
0

If I understood your question right, you need to filter out non-ASCII characters? Please confirm whether this is true. In order to do that, have a look at REGEXP matching as in the comment link and this question.

Try something like

SELECT * FROM `table ` WHERE `X` REGEXP 'Helloworld';
Community
  • 1
  • 1
Alexey
  • 3,414
  • 7
  • 26
  • 44
  • 1
    `Helloworld` doesn't match `hello!World` – Toto Apr 28 '15 at 12:56
  • Yes, but this doesn't do the trick, I still don't get the row – Stefanos Chrs Apr 28 '15 at 12:56
  • @Toto: you're right, the answer is not right. Not sure if it's better to delete it. There was a discussion on filtering non-alpha characters here: http://stackoverflow.com/questions/6942973/mysql-how-to-remove-all-non-alpha-numeric-characters-from-a-string . As the accepted answer says it's better to store filtered values. Then we don't need to filter every row on every query (huge performance drawback) and it can be indexed. – Alexey Apr 28 '15 at 13:08
0

What you can do is to replace all special characters like this:

SELECT * FROM table WHERE LOWER(REPLACE(X, '!', '')) = LOWER('HelloWorld');

Chain those replacements if you have to replace more:

SELECT * FROM table WHERE LOWER(REPLACE(REPLACE(X, '!', ''), '?', '')) = LOWER('HelloWorld');
fancyPants
  • 50,732
  • 33
  • 89
  • 96
  • 1
    and then yet thousand REPLACE() calls :) Sorry, I did not realize there was reasonable number of "special" characters meant in the original post. As a generic solution I don't think it works well – Alexey Apr 28 '15 at 13:19
0
REGEXP 'hello[^[:alpha:]]*world'

Notes:

  • This finds the string in the middle of other stuff; add ^ and $ to anchor to ends.
  • This assumes the non-alpha character(s) are between hello and world, not some other spot in the string.
  • This relies on the relevant collation to do (or not do) case folding.
Rick James
  • 135,179
  • 13
  • 127
  • 222