Replacing certain characters of string in column (MySQL)

Question

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.

If your MySQL is version 8 (or up) consider using regular expressions. — PM 77-1
– PM 77-1, Commented Jan 4, 2021 at 23:00
Try escaping name with backticks since it is a MySQL keyword. — Parfait
– Parfait, Commented Jan 5, 2021 at 1:23

Gordon Linoff · Accepted Answer · 2021-01-04 23:00:00Z

1

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.

answered Jan 4, 2021 at 23:00

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Replacing certain characters of string in column (MySQL)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related