Why is this regexp query not returning any results?

Question

Good day

I have a field that have rows looking like

BBB-888-8557ZZV-003.XYZ
BBB-999-8787ZZV-00D.XYZ

I need to find (and replace after I confirmed that my select returns the correct rows :)) all records that match the regex:

/-\d\d[A-Z]/g

Hence all fields that have a minus sign followed by two digits followed by an alphabet letter.

I also only need to filter out the fields that have 999 in them.

I tried the following SQL but it returns no results:

SELECT * 
FROM  `track` 
WHERE (
pod LIKE  "BBB-999%"
AND pod
REGEXP  '/-\d\d[A-Z]/g'
)

Also, what will be a quick why to replace all of these fields once they are found to:

BBB-999-8787ZZV-_MARK_D.XYZ

Answer 1

Numbers in mysql shouls be matched like [0-9] or [[:digit:]]
\d wont work as you expected. link

try;

SELECT * 
FROM  `track` 
WHERE
pod LIKE  "BBB-999%" and
pod REGEXP  '\-[0-9]{2}[A-Z]{1}'

or

SELECT * 
FROM  `track` 
WHERE
pod LIKE  "BBB-999%" and
pod REGEXP  '\-[[:digit:]]{2}[A-Z]{1}'

sql fiddle demo

Update the field having -00[A-Z].XYZ with -MARK[A_Z].XYZ

1. Then length of the string is varying (from comment)
2. last 8 characters can be either \-[0-9]{3}.XYZ or \-[0-9]{2}[A-Z]{1}.XYZ

try:

UPDATE `track`
SET `pod` = concat(reverse(substr(reverse( `pod` ) from 8)), 'MARK', substr( `pod`  from -5)) 
WHERE
`pod` LIKE  "BBB-999%" and
`pod` REGEXP  '\-0{2}[A-Z]{1}';

sql fiddle demo

Answer 2

You can use the CONCAT() function to get the replacement in the output, after restricting with your query.

SELECT CONCAT(SUBSTRING(pod, 1, LENGTH(pod) - LOCATE('-', REVERSE(pod)) + 1),
              '_MARK_D.XYZ')
FROM `track`
WHERE
(
    pod LIKE  "BBB-999%"
    AND pod REGEXP  '-[0-9][0-9][A-Z]'
)

For some reason, I needed to use [0-9] in the MySQL regex to get it to work.

Click the link below for a running demo:

SQLFiddle

If you want actually change the values in the pod column you can try an UPDATE:

UPDATE `track`
SET pod = CONCAT(SUBSTRING(pod, 1, LENGTH(pod) - LOCATE('-', REVERSE(pod)) + 1),
                 '_MARK_D.XYZ')
WHERE
(
    pod LIKE  "BBB-999%"
    AND pod REGEXP  '-[0-9][0-9][A-Z]'
)