Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Replace a substring found using Regex in a column

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"

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I ended up doing the update table-wide this way. What it is doing is searching for any substring in the column notetext that is 45 characters or bigger and then inserting a space at index 35 in that substring and updating the column. Middleware should have checked this, it does now. This is just to update the old data. I had to run the same statement many times because there were substring longer than 100s of characters and this statement only takes one breakup at index 35 at a time.

    UPDATE mytable SET notetext = REPLACE(notetext,regexp_substr(notetext,'[a-zA-Z0-9_.:/-]{45,}') ,INSERT(regexp_substr(notetext,'[a-zA-Z0-9_.:/-]{45,}'),35,0, ' ') ) WHERE notetext REGEXP '[a-zA-Z0-9_.:/-]{45,}';

  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search