mysql replace string + next one char

Question

Is it possible to REPLACE a string + next character in MySQL? Something like LIKE underscore.

For example, if text column is this: 12 13 14 14_B 15 14_A, REPLACE all 14_* with an empty character, and replaced text should be: 12 13 14 15

Could you refine or elaborate on your example. It is unclear how you arrived at the *result*. — Jason McCreary, Oct 22 '12 at 20:36
@JasonMcCreary: result means replaced text (final text).. edited. — mrdaliri, Oct 22 '12 at 20:39

score 3 · Accepted Answer · edited May 23 '17 at 11:56

You'll be looking to do this using a regular expression UDF in MySQL. Key ingredients are

regular expression UDF - check here
The regular expression itself

If you will ONLY ever see 2 to 4 of these that you need replaced, a poor man's working approach (SQL Fiddle):

SELECT *,IF(LOCATE('14_',B)+3<=Length(B),
         INSERT(B,LOCATE('14_',B),4,''),B) C
FROM
(
  SELECT *,IF(LOCATE('14_',A)+3<=Length(A),
           INSERT(A,LOCATE('14_',A),4,''),A) B
  FROM (
    SELECT *,IF(LOCATE('14_',x)+3<=Length(X),
             INSERT(X,LOCATE('14_',x),4,''),X) A
    FROM X
  ) Q1
) Q2

I've only catered for 3 replacements but you can easily expand the pattern. Include only the columns from the base table needed in the outermost query.

mysql replace string + next one char

1 Answers1