How to remove some special character from database column in where condition to match another string?

Question

I have a read only database. where i want to compare two string. But in the database some column value name has included some special character but i want to compare another string with this string without special character.

For example, In DB the column value name is BMW 3-SERIES. And i have another string BMW3SERIES. So, i want to remove - and space then compare two string in a where condition. Is both string equal or not ?

I am stuck how to remove special characters from model_name .

SELECT * FROM VENDOR WHERE model_name = "BMW3SERIES"

What should be the query?

score 1 · Answer 1 · answered May 10 '22 at 05:11

1

On MySQL 8+, you could use REGEXP_REPLACE to remove what you deem as special characters, for example:

SELECT *
FROM VENDOR
WHERE REGEXP_REPLACE(model_name, '[ -]+', '') = 'BMW3SERIES';

You may add whatever characters you wish to the above character class [ -]+.

answered May 10 '22 at 05:11

Tim Biegeleisen

502,043
27
286
360

I executed this query => SELECT * FROM `tb_vehicle_models` WHERE brand_id = 1 and REGEXP_REPLACE(model_name, '[ -]+', '') = 'TSX' Got error : Error 1370: execute command denied to user – Md. Abu Farhad May 10 '22 at 06:39
What is your version of MySQL? If < 8, my answer won't work. – Tim Biegeleisen May 10 '22 at 06:40
My version of MySQL is 8.0.28 – Md. Abu Farhad May 10 '22 at 06:42
Your version is fine, you may have some permissions issue going on. – Tim Biegeleisen May 10 '22 at 06:42
Without REGEXP_REPLACE my query execute fine . For REGEXP_REPLACE is there need any specific permissions? – Md. Abu Farhad May 10 '22 at 06:44
Not sure, it could actually still be a version issue. – Tim Biegeleisen May 10 '22 at 06:45
@Md.AbuFarhad See [Mysql: execute command denied to user ''@'localhost' for routine error](https://stackoverflow.com/questions/6434573) – Wiktor Stribiżew May 10 '22 at 09:54
Tyou probably need to swap the dash and space. – Rick James May 13 '22 at 03:38
@RickJames No typo whatsoever. Dash appearing anywhere _besides_ the final position in a character class might be interpreted as forming a range, which isn't what my intention is. You should review regular expressions. – Tim Biegeleisen May 13 '22 at 03:45
@TimBiegeleisen - Seems to be "besides _first or last_ position. (Maybe we are both mistaken??) – Rick James May 13 '22 at 03:55

How to remove some special character from database column in where condition to match another string?

1 Answers1