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 :frowning:

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!

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.

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

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

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

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.