I have the following column in my Table name:STAT It would be helpful if someone can assist in writing a mysql script I would like to split the strings in to 2 columns. However the split should happen only based on the last word, i.e the Region as can be seen in the example below. There is an exception that IF the region is North America then the split should be done considering the last two words, see below The script should add the new column to either my existing table named : STAT or to a new table.
--------------------- Name --------------------- P1 xx ASPAC P1 yy EMEA P1 zz uu EMEA P2 oouu_xsh-s EMEA P2 xx tt North America --------------------- P2 Region --------------------- P1 xx ASPAC P1 yy EMEA P1 zz uu EMEA P2 oouu_xsh-s EMEA P2 xx tt North America
Advertisement
Answer
If you are using MySQL 8.0, you can use the REGEXP_REPLACE() function to split the process the string with a regexp.
Regex ^(.*) (.*)$ splits the string in two : everything before the last space in the string goes to special variable $1, and everything after the last space goes to $2. Special care need to be taken when the string ends with North America.
Consider :
WITH data AS (
SELECT 'P1 xx ASPAC' name UNION SELECT 'P1 yy EMEA' UNION SELECT 'P1 zz uu EMEA'
UNION SELECT 'P2 oouu_xsh-s EMEA' UNION SELECT 'P2 xx tt North America'
)
SELECT
name,
CASE
WHEN name like '% North America' THEN REPLACE(name, ' North America', '')
ELSE REGEXP_REPLACE(name, '^(.*) (.*)$', '$1')
END P2,
CASE
WHEN name like '% North America' THEN 'North America'
ELSE REGEXP_REPLACE(name, '^(.*) (.*)$', '$2')
END Region
FROM data
name | P2 | Region :--------------------- | :------------ | :------------ P1 xx ASPAC | P1 xx | ASPAC P1 yy EMEA | P1 yy | EMEA P1 zz uu EMEA | P1 zz uu | EMEA P2 oouu_xsh-s EMEA | P2 oouu_xsh-s | EMEA P2 xx tt North America | P2 xx tt | North America