REGEXP_REPLACE() on MySQL 5.7.27

[fumeeptc]

Hello there!

I made a query on one of the projects i m working on (On MariaDB 10.1.37):
SELECT * FROM table WHERE REGEXP_REPLACE(substring_index(product,' ',1), '[^a-zA-Z ]', '')='VALUE'

Where goal was:

1. from value select part till first space: example VALUE1 SOME OTHER to get VALUE1
2. to remove numbers and any other symbol: example VALUE1 to get VALUE

And query above does the trick as needed!
Issue is that on client side there is MySQL 5.7.27 and as we know REGEXP_REPLACE() came in MySQL on 8+ version

For now there are no options to upgrade client side to MySQL 8+ or migrate to MariaBD so the question is how and can i achieve the same result in MySQL 5.7.27?

I tried to search and tried WHERE substring_index(product,' ',1) REGEXP '^a-zA-Z' = 'VALUE' but REGEXP returns 1 or 0 and it is not what works for me. …

Any help?

Thanks in advance!

[fumeeptc]

This is not a final answer and if someone have a good or closer example to what i have asked then go ahead and post an answer. I would like to see it!

What i did is i just used LIKE

SELECT * FROM table WHERE substring_index(product,' ',1) LIKE 'VALUE%'

I know that this can show wrong data for some case but for my needs this way is acceptable.

[kocdilek1977]

why not using substring(); when displaying it on site instead of deleting from mysql? example: pass the values to array and delete number in values with a filter function.

[fumeeptc]

I do not need load data that i do not need.

[benanamen]

Hi @fumeeptc,
Could you please post an actual data value and the expected result from the query.