0

I'm attempting to search records by passing in a squashed version of a column - If I pass in "Martin" I would expect the matching "M-@art*in" would be returned. This is something that REGEX_REPLACE seems to handle well in MySQL 8.0+ and I've seen many recommendations for User Defined Functions (UDFs) that can do the same job pre-8.0. My supervisor doesn't want to rely on UDFs, and so I am wondering if there are any other options?

Any help is greatly appreciated.

BenG
  • 3
  • 3
  • Are you just trying to search, or change values? You could use [REGEXP](https://dev.mysql.com/doc/refman/5.7/en/regexp.html#operator_regexp) to search. But no, there's no equivalent pre-8.0 to the REGEXP_REPLACE() function. Only REPLACE() which does not support regular expressions. – Bill Karwin Aug 11 '22 at 16:31
  • That said, you should be thinking about upgrading to MySQL 8.0 anyway, because MySQL 5.7 is due to reach its end of life in October 2023. – Bill Karwin Aug 11 '22 at 16:54

2 Answers2

0

Use something like DBD::mysql, extract the column and update them.

Or, come to think of it, set your collation to UTF8

hd1
  • 33,938
  • 5
  • 80
  • 91
0

If you have a specific set of characters allowed, you could do e.g. replace(replace(replace('M-@art*in','-',''),'@',''),'*','')

If not, you can have a somewhat cumbersome query that looks at each character individually and tests if it should be included in a group_concat that reassembles them:

select id, group_concat(substr(bar,i,substr(bar,i,1) regexp '[a-z]') order by i separator '') clean_bar
from (select 1 i union select 2 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10) i
join foo on i <= length(bar)
group by foo.id

fiddle

But you'd probably want to run that on the data and store the squashed version in a separate column if you are trying to search on it.

Note that mysql 5.7 will no longer be supported in 14 months; do plan to upgrade.

ysth
  • 96,171
  • 6
  • 121
  • 214