I have a table which looks like this:

I'm trying to figure out how to get separate the dashes to get different values into different columns. So the first word before the first dash ('-') would populate the 'Company' column. I would also like to get the word after the first dash and populate that into the 'City' column. Finally, I would also like to get the last part of the string after the second dash to populate that into my 'Country' column.

So the goal is to have the table updated like this:

Is this possible? Some of the values in the Customer_Group column either have a white space or no white pace before and after the dash ('-') character but I was also hoping to remove the white space that may appear before the word when it is inserted into the new columns.

CodePudding user response：

We can use REGEXP_SUBSTR() here:

SELECT
    Customer_Group,
    REGEXP_SUBSTR(Customer_Group, '^(\S ?( [^\s-] )*)[ ]*-', 1, 1, NULL, 1) AS Company,
    REGEXP_SUBSTR(Customer_Group, '-[ ]*(\S ( \S )*)[ ]*-', 1, 1, NULL, 1) AS City,
    REGEXP_SUBSTR(Customer_Group, '-[ ]*([^\s-] ( [^\s-] )*)$', 1, 1, NULL, 1) AS Country
FROM yourTable;

Demo

CodePudding user response：

Using INSTR(), SUBSTR and TRIM

UPDATE your_table
SET Company = TRIM(SUBSTR(Customer_Group, 1, INSTR(Customer_Group, '-') - 1)),
    City = TRIM(SUBSTR(Customer_Group, INSTR(Customer_Group, '-')   1, INSTR(Customer_Group, '-', INSTR(Customer_Group, '-')   1) - INSTR(Customer_Group, '-') - 1)),
    Country = TRIM(SUBSTR(Customer_Group, INSTR(Customer_Group, '-', INSTR(Customer_Group, '-')   1)   1))