Replacing strings in MySQL?

Question

I have those strings in y table:

abcdefg_1056-DF or
123erttzz-1292 or
gdfgdfg_1056

What I want is only the first part, abcdefg for example for the first string. So I can replace all numbers and all -DF with an empty string, but I dont know how.

Ideas?

score 1 · Answer 1 · edited May 23 '17 at 10:34

1

I would suggest a regex replace. Have a look here - I think this might help you:

How to do a regular expression replace in MySQL?

edited May 23 '17 at 10:34

Community

1
1

answered Jun 05 '12 at 08:19

Michi Werner

316
3
6

score 1 · Answer 2 · answered Jun 05 '12 at 08:29

If you prever a lazy and ugly method (and not recommended for very, very much rows), you can spare yourself a user defined function like in mwerner's answer and do it simply like this:

select 
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(asdf, '0', ''), '1', ''), '2', ''), '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8', ''), '9', ''), '_', ''), '-DF', '')
from
(
select
'abcdefg_1056-DF' as asdf
union select
'123erttzz-1292'
union select
'gdfgdfg_1056'
)q

Replacing strings in MySQL?

2 Answers2