How to update the value of a column through substitution in all rows of the table?

Navigation
4

I have a i18n table in the database whose constant column contains all values with the prefix lang :

Table description 

┌──────────────┬──────────────┬──────┬────────┬─────────────────────┬────────────────┐
│ Field        │ Type         │ Null │ Key    │ Default             │ Extra          │      
├──────────────┼──────────────┼──────┼────────┼─────────────────────┼────────────────┤
│ id           │ int(13)      │ NO   │ PRI    │ NULL                │ auto_increment │ 
├──────────────┼──────────────┼──────┼────────┼─────────────────────┼────────────────┤
│ type_id      │ int(13)      │ NO   │        │ NULL                │                │ 
├──────────────┼──────────────┼──────┼────────┼─────────────────────┼────────────────┤
│ i18n_id      │ varchar(3)   │ NO   │        │ NULL                │                │ 
├──────────────┼──────────────┼──────┼────────┼─────────────────────┼────────────────┤
│ constant     │ varchar(255) │ NO   │        │ NULL                │                │ 
├──────────────┼──────────────┼──────┼────────┼─────────────────────┼────────────────┤
│ value        │ tinytext     │ NO   │        │ NULL                │                │ 
├──────────────┼──────────────┼──────┼────────┼─────────────────────┼────────────────┤
│ description  │ varchar(255) │ NO   │        │ NULL                │                │ 
├──────────────┼──────────────┼──────┼────────┼─────────────────────┼────────────────┤
│ date_created │ timestamp    │ NO   │        │ 0000-00-00 00:00:00 │                │
├──────────────┼──────────────┼──────┼────────┼─────────────────────┼────────────────┤
│ date_updated │ timestamp    │ NO   │        │ CURRENT_TIMESTAMP   │                │
└──────────────┴──────────────┴──────┴────────┴─────────────────────┴────────────────┘

Content Example

Example of content in column constant : 

LANG_WORD_JOHN
LANG_WORD_DOE

What I'm trying to do is to update all the records in that table by changing the prefix LANG to I18N in the contents of the constant column, but so far unsuccessful! 

I18N_WORD_JOHN
I18N_WORD_DOE

Question

How can I update the value of the constant column on all existing rows in the table by replacing the prefix LANG with I18N ?

    
asked by anonymous 31.12.2013 / 15:11

1 answer

3

You can perform the operation simply by using REPLACE (en) , which is part of the MySQL text manipulation functions: 

UPDATE i18n SET constant = REPLACE(constant, 'LANG_', 'I18N_');
    
31.12.2013 / 16:16
Error while executing method nls in R - 'arg' must be NULL or a character vector How to leave the background of a fixed div in iOS (iPad) Safari?