CODEWITHSUNDEEP
mysqlmariadb
Ingus
Ingus

Reputation: 1044

looking for REGEXP_REPLACE() alternative on MySQL 5.7.27

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 as i need value. …

Any help?

Thanks in advance!

Upvotes: 1

Views: 11515

Answers (3)

Jake Coburn
Jake Coburn

Reputation: 1

This works in MySQL 5.7... This example would be extracting a UTM_Source parameter value from a URL's query string:

 case when url like '%utm_source%' then
       substring(
       url,
      locate('utm_source=',url)+char_length('utm_source='),
      case when locate('&',url,locate('utm_source=',url)) = 0 then char_length(url)+1 else locate('&',url,locate('utm_source=',url)) end - (locate('utm_source=',url)+char_length('utm_source=')) 
      ) else NULL end utm_source;

Upvotes: 0

Coding_Maeda
Coding_Maeda

Reputation: 61

'Been searching for a solution as well since my mates are on MYSQL 5.5 and therefore they don't have REGEXP_REPLACE() either.

It's easy to find the solution on stackOverFlow for replacing just one char, but couldn't find for a string, therefore I've created that piece of code, it could help someone :

SET @string = 'I love shop it is a terrific shop, I love eveything about it';
SET @shop_code = 'shop';

SET @shop_date = CONCAT(@shop_code, '__', DATE_FORMAT(NOW(), '%Y_%m_%d__%Hh%im%ss'));

SET @part1 = SUBSTRING_INDEX(@string, @shop_code, 1);

SET @shop_nb = ROUND( (LENGTH(@string) - LENGTH(REPLACE(@string, @shop_code,''))) / LENGTH(@shop_code) );

SET @part2 = SUBSTRING_INDEX(@string, @shop_code, -@shop_nb);

SET @string = CONCAT(@part1, @shop_date, @part2);

SELECT @string;

Upvotes: 1

Ingus
Ingus

Reputation: 1044

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.

Upvotes: 0

Related Questions