CODEWITHSUNDEEP
mysqlsqlsql-update
user6412824
user6412824

Reputation:

how to split a string in field data MySQL

In locale for an example(id-id)

I want update query for this locale

How I need to split the string hyphen should change to underscore, last 2 digits should be upper case

o/p should be: id_ID

I've been tried this query 

UPDATE  substring_index(locale,'-',1)=substring_index(locale,'-',1),
locale=REPLACE(locale,'-','_'),
substring_index(locale,'-',-1)=UPPER(substring_index(locale,'-',-1))
jlg_language_code_mapping;

Please help me guys..!

enter image description here

Upvotes: 0

Views: 71

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

Here's one option:

SQL> with test (id, locale) as
  2    (select 'arabic', 'ar'                  from dual union all
  3     select 'indonesian', 'id-id'           from dual union all
  4     select 'malay', 'ms-my'                from dual union all
  5     select 'bulgarian', 'bg'               from dual union all
  6     select 'chinese (simplified)', 'zh-cn' from dual
  7    )
  8  select id, locale,
  9    regexp_substr(locale, '^\w+') ||
 10    replace(upper(regexp_substr(locale, '-\w+$')), '-', '_') new_locale
 11  from test;

ID                   LOCALE                    NEW_LOCALE
-------------------- ------------------------- -------------------------
arabic               ar                        ar
indonesian           id-id                     id_ID
malay                ms-my                     ms_MY
bulgarian            bg                        bg
chinese (simplified) zh-cn                     zh_CN

SQL>

Update:

update your_table set
  locale = regexp_substr(locale, '^\w+') ||
           replace(upper(regexp_substr(locale, '-\w+$')), '-', '_');

Upvotes: 1

Related Questions