1

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?

The Impaler
  • 45,731
  • 9
  • 39
  • 76
Mefenamic
  • 38
  • 7
  • Have you tried like with '%word\'s%' ? – skelwa Aug 07 '18 at 09:44
  • You could replace the * selector by only columns you want to select. Your query would probably run faster. – Jojoes Aug 07 '18 at 09:44
  • @skelwa i should assume that the search keyword is "words" not word's. I am after the value of the column. – Mefenamic Aug 07 '18 at 09:51
  • The starting % in your LIKE clause causes the index for this column to not to be used. If you only want words starting with some string, try removing starting '%' or else have a look at MySQL full text index. – skelwa Aug 07 '18 at 09:56
  • @Jojoes what do you mean? 'select column_value from .....'. This doesn't matter. I think the work is on the sql filters inside Where clause – Mefenamic Aug 07 '18 at 09:57
  • @Mefenamic as you want ... but more content about the topic can be found [here](https://stackoverflow.com/questions/3180375/select-vs-select-column) and [here](https://stackoverflow.com/questions/3639861/why-is-select-considered-harmful) – Jojoes Aug 07 '18 at 10:15
  • @Jojoes i don't know if you get my inquiry correctly. Please reread. i got no problem with selecting all rows. my problem is filtering returned data with special characters. – Mefenamic Aug 07 '18 at 16:04
  • @Mefenamic you said you wanted fast runtime, so I proposed a possible optimization. It doesn't relate to the filter, that's why I wrote a comment. – Jojoes Aug 09 '18 at 11:36
  • Add these to your example and show which ones to return: `words`, `x-word`, `word-smith`, `foo-bar`. (The statement of the problem is still unclear.) – Rick James Aug 24 '18 at 04:54

2 Answers2

1

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.

sticky bit
  • 36,626
  • 12
  • 31
  • 42
  • thanks for this @sticky-bit. this helps much. I would rather use the replace but kinda long for all characters. Furthermore, how can we do it in full-text search? can't use the replace inside match(replace()). – Mefenamic Aug 08 '18 at 02:11
0

You must add an index on your column_value. MySQL doc

Athos
  • 181
  • 6