regular expression or replace function in where clause of a mysql query.

Question

Hi All,

I write a mysql query

select * from table where name like '%salil%'

which works fine but it will no return records with name 'sal-il', 'sa@lil'.

So i want a query something like below

select * from table whereremove_special_character_from(name)like '%salil%'

remove_special_character_from(name) is a mysql method or a regular expression which remove all the special characters from name before like executed.

Answer 1

No, mysql doesn't support regexp based replace.
I'd suggest to use normalized versions of the search terms, stored in the separate fields.

So, at insert time you strip all non-alpha characters from the data and store it in the data_norm field for the future searches.

Answer 2

Since I know no way to do this, I'd use a "calculated column" for this, i.e. a column which depends on the value of name but without the special characters. This way, the cost for the transformation is paid only once and you can even create an index on the new column.

See this answer how to do this.

Answer 3

I agree with Aaron and Col. Shrapnel that you should use an extra column on the table e.g. search_name to store a normalised version of the name.

I noticed that this question was originally tagged ruby-on-rails. If this is part of a Rails application then you can use a before_save callback to set the value of this field.

Answer 4

In MYSQL 5.1 you can use REGEXP to do regular expression matching like this

   SELECT * FROM foo WHERE bar REGEXP "baz"

see http://dev.mysql.com/doc/refman/5.1/en/regexp.html

However, take note that it will be slow and you should do what others posters suggested and store the clean value in a separate field.