I have a table which is like this:

I'm trying to split the CUSTOMER_GROUP field into the Company, City and Country fields so that my target table will look like this:

I have tried this SQL code but it looks at the first dash therefore separates the 'IT' from 'LND' when I need to keep these together as the company name:

UPDATE my_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))

The code above separates my string like this:

This is not what I want as the company should be called 'IT-BOUSH'. Is there a way where I can ignore the first dash ('-') then split the rest of the string out from the second dash onwards to get my target table?

CodePudding user response：

substr instr it is.

Sample data:

SQL> with test (cust_id, customer_group) as
  2    (select 1927373, 'IT-BOUSH - Swindon- United Kingdom' from dual union all
  3     select 1928373, 'IT-BOUSH - Madrid - Spain'          from dual
  4    )

Query:

  5  select cust_id,
  6    trim(substr(customer_group, 1, instr(customer_group, '-', 1, 2) - 1)) company,
  7    --
  8    trim(substr(customer_group,
  9                instr(customer_group, '-', 1, 2)   1,
 10                instr(customer_group, '-', 1, 3) - instr(customer_group, '-', 1, 2) - 1
 11               ))
 12          city,
 13    --
 14    trim(substr(customer_group, instr(customer_group, '-', 1, 3)   1)) country
 15  from test;

   CUST_ID COMPANY         CITY            COUNTRY
---------- --------------- --------------- --------------------
   1927373 IT-BOUSH        Swindon         United Kingdom
   1928373 IT-BOUSH        Madrid          Spain

SQL>