Replacing certain characters of string in column (MySQL)

I have a table in a MySQL database that features a ‘name’ column with values such as:

• SDP3973455/1
• 101390375/2
• SDP4608677
• 105859492/1
• 104295947

As you can see, some values have the characters ‘SPD’ in front and some values have ‘/1 or /2’ at the end of the string. I want to keep only the numbers inbetween these characters. So ‘SDP3973455/1’ would be replaced with ‘3973455’ and ‘105859492/1’ would be replaced with ‘105859492’.

I have tried using the following query but I get this message ‘0 row(s) affected Rows matched: 2171368 Changed: 0 Warnings: 0’:

UPDATE myTable
SET name = REPLACE(name, 'SDP', '');

If anybody can point me in the right direction that would be very much appreciated. Also if you can figure out how to delete ‘/1’ or ‘/2’ that would be wonderful.

Answer

That would suggest that your column has no 'SPD' in it.

One problem might be intervening characters. Presumably, this query returns no rows:

select t.*
from myTable t
where name like '%SPD%';

You could then try a more general pattern:

select t.*
from myTable t
where name like '%S%P%D%';

Or perhaps the characters are from an extended character set.