Home > other >  How to split a string with multiple characters into separate columns in SQL?
How to split a string with multiple characters into separate columns in SQL?

Time:02-01

I have a table which looks like this:

Cust_ID Customer_Group Company City Country
1927373 Microsoft - Liverpool - United Kingdom
1928373 Google - Mumbai - India

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:

Cust_ID Customer_Group Company City Country
1927373 Microsoft - Liverpool - United Kingdom Microsoft Liverpool United Kingdom
1928373 Google-Mumbai-India Google Mumbai India

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))
  • Related