MySQL add text to a field

Question

I have an articles table and I need to add the year to the name

Before

Bicicleta rodado 20", freestyle Mirraco Blend
Bicicleta rodado 20", freestyle Mirraco Blend (azul)
Bicicleta rodado 20", freestyle Mirraco Blend (negro)
Bicicleta rodado 20", freestyle Mirraco Blink
Bicicleta rodado 20", freestyle Mirraco Blink (blanco con azul)
Bicicleta rodado 20", freestyle Mirraco Blink (marron)
Bicicleta rodado 20", freestyle Mirraco N°7
Bicicleta rodado 20", freestyle Mirraco N°7 (negro con blanco)
Bicicleta rodado 20", freestyle Mirraco N°7 (purpura con blanco)

After

Bicicleta rodado 20", freestyle Mirraco Blend 2010
Bicicleta rodado 20", freestyle Mirraco Blend 2010 (azul)
Bicicleta rodado 20", freestyle Mirraco Blend 2010 (negro)
Bicicleta rodado 20", freestyle Mirraco Blink 2010
Bicicleta rodado 20", freestyle Mirraco Blink 2010 (blanco con azul)
Bicicleta rodado 20", freestyle Mirraco Blink 2010 (marron)
Bicicleta rodado 20", freestyle Mirraco N° 7 2010
Bicicleta rodado 20", freestyle Mirraco N° 7 2010 (negro con blanco)
Bicicleta rodado 20", freestyle Mirraco N° 7 2010 (purpura con blanco)

The rule would be to add the year before the parenthesis. And if there is no parenthesis, add it at the end. It can't be counting word because the count is not always the same (N° 7 in this particular case)

fncomp · Accepted Answer · 2017-04-30 20:28:56Z

2

This should get what you want if you are looking to inject the date:

SELECT 
  CASE
    WHEN (INSTR( your_field,  '(' ) > 0)
      THEN REPLACE(your_field, '(', '2010 (')
    ELSE CONCAT (your_field, ' 2010')
  END 
FROM your_table;

edited Apr 30, 2017 at 20:28

answered Nov 17, 2010 at 3:42

fncomp

6,2084 gold badges36 silver badges43 bronze badges

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

Comments

Nelson Miranda · Accepted Answer · 2010-11-17 03:49:29Z

0

Try this:

SELECT
case instr(accountcode,'-') when instr(accountcode,'-') = 0 then 
concat(accountcode, '  2010') else 
concat(left(accountcode,instr(accountcode,'-')-1), concat(' 2010',right(accountcode,instr(accountcode,'-')-2))) 
end 
FROM account a;

answered Nov 17, 2010 at 3:49

Nelson Miranda

5,5625 gold badges37 silver badges58 bronze badges

1 Comment

Nelson Miranda Over a year ago

Sorry it happens to me too, I didn't see the result with my data, let me check it out

Collectives™ on Stack Overflow

MySQL add text to a field

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related