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:
- from value select part till first space: example
VALUE1 SOME OTHERto get
- to remove numbers and any other symbol: example
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. …
Thanks in advance!