CODEWITHSUNDEEP
sqlregexoracle-databaseplsql
Suman
Suman

Reputation: 93

Problem with using regular expression in SQL

I have a column with name "data-description" which has entries like

Mumbai,Maharastra,India
London,London, Britain
Chandigarh,Haryana,India
Lahore, Punjab, Non-India

Each line describes the value of one data-description column.

Now I need to update another table which will have three columns which will have 

<City> => <Citycode>   ---> Every City 
<Statename> => <Statename>
<Country>  => <Country>

I have a table where a mapping between City and City-Code is made. Now I need to make another table with the three columsn Citycode, Statename, Country

How do I do this with a couple of SQl statements and without using any PL/SQL?

Also, The order may not be the same in rows. Like some rows have order City,State,County. Others may have the order State,Country,City.

Upvotes: 1

Views: 311

Answers (1)

Vincent Malgrat
Vincent Malgrat

Reputation: 67802

you can use REGEXP_SUBSTR to extract information from your source column:

SQL>  WITH my_data AS (
  2      SELECT 'Mumbai,Maharastra,India' d FROM dual
  3      UNION ALL SELECT 'London,London, Britain' d FROM dual
  4      UNION ALL SELECT 'Chandigarh,Haryana,India' d FROM dual
  5      UNION ALL SELECT 'Lahore, Punjab, Non-India' d FROM dual
  6   )
  7   SELECT regexp_substr(d, '[^, ]+', 1, 1) City,
  8          regexp_substr(d, '[^, ]+', 1, 2) Statename,
  9          regexp_substr(d, '[^, ]+', 1, 3) Country
 10     FROM my_data;

CITY                      STATENAME                 COUNTRY
------------------------- ------------------------- -------------------------
Mumbai                    Maharastra                India
London                    London                    Britain
Chandigarh                Haryana                   India
Lahore                    Punjab                    Non-India

Upvotes: 3

Related Questions