0

Given a table with a column called files_name, and you want to match everything after the match with regex, and replace it with nothing.

I have this code but it matches everything, but it's always changed 0.

UPDATE files 
SET files_name = REGEXP_REPLACE (files_name, 'S[0-9]?[0-9]?[0-9]E[0-9]?[0-9]\K.*', '') 
WHERE type = 5;

A online regex tester says my regex is correct, and is matching as desired, yet no matter what I put into the second '' it never replaces the match.

I'm using mariadb 10.3.17

1
UPDATE files 
SET files_name =
    REGEXP_REPLACE(
       files_name,
       '(S[0-9]?[0-9]?[0-9]E[0-9]?[0-9])([^0-9].*)',
       '\\1'
    ) 
WHERE type = 5;

fiddle

UPDATE

I executed this command, with redacted info of course...

mariadb -h ip -P port -D database -e "UPDATE files SET files_name = REGEXP_REPLACE (files_name, '(S[0-9]?[0-9]?[0-9]E[0-9]?[0-9])([^0-9].*)', '\\1') WHERE type = 5;" -pPASSWORD

You need to quote a slash:

  1. in command line
  2. in SQL query

So you would use '\\\\1'.

You may avoid using slash by using CONCAT(CHAR(92), '1') instead of '\\1' in the query text - in that case no slaches in SQL and quoting don't need in.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.