I have a table in MySQL 8. In a varchar column, I want to replace long substrings by another substrings. I can find those rows using this regex:
SELECT notetext FROM mytable WHERE notetext REGEXP '[a-zA-Z0-9_.:\/-]{25,}';
This Regex searches for all rows that have substrings with length>25. However, I cannot figure out how to replace the matched substrings by a replacement substring. I want to do something like this:
update mytable set notetext = replace(notetext,** regex match ***, 'replacement text') WHERE notetext REGEXP '[a-zA-Z0-9_.:\/-]{25,}';
Suppose notetext is "this is the tracking number 8868848848838883399999499494949949949949". When this is printed somewhere, it overshoots a print area. Intent is to break this tracking number by adding a space so that the text is wrapped when printed so the overall text remain in the print area of a fixed length and the print system does not wrap if there is no break in a long word.
"this is the tracking number 8868848848838883399 999499494949949949949"
>Solution :
I think you’re trying to find first 25 characters …
If it’s, use the query:
set @st = 'this is the tracking number 8868848848838883399 999499494949949949949';
select REGEXP_REPLACE(@st, '.*', '', 25) as trim_str;
sample code: db<>fiddle