CODEWITHSUNDEEP
mysqlsqldatabasespecial-characters
Mefenamic
Mefenamic

Reputation: 38

MySQL - Special characters in column value

I got a big data (approximately 600,000).

I want the rows with value "word's" will appear.

Special characters will be completely ignored.

TABLE:

| column_value  |
| ------------- |
| word's        |
| hello         |
| world         |

QUERY: select * from table where column_value like '%words%'

RESULTS:

| column_value  |
| ------------- |
| word's        |

I want the rows with special characters will appear and ignore their special characters.

Can you please help me how can we achieve it with fast runtime?

Upvotes: 1

Views: 2741

Answers (2)

sticky bit
sticky bit

Reputation: 37472

You can use replace to remove the "special" character prior the matching.

SELECT *
       FROM table
       WHERE replace(column_value, '''', '') LIKE '%words%';

Nest the replace() calls for other characters.

Or you try it with regular expressions.

SELECT *
       FROM table
       WHERE column_value REGEXP 'w[^a-zA-Z]*o[^a-zA-Z]*r[^a-zA-Z]*d[^a-zA-Z]*s';

[^a-zA-Z]* matches optional characters, that are not a, ..., y and z and not A, ..., Y and Z, so this matches your search word also with any non alphas between the letters.

Or you have a look at the options full text search brings with it. Maybe that can help too.

Upvotes: 1

Athos
Athos

Reputation: 181

You must add an index on your column_value. MySQL doc

Upvotes: 0

Related Questions