Problem with using regular expression in SQL

Question

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.

So essentially you want to extract the value from this single column into 3 columns which you can then proceed to use in your query, e.g to JOIN to another table with? — Chris Cameron-Mills
– Chris Cameron-Mills, Commented Jan 17, 2011 at 13:14

Vincent Malgrat · Accepted Answer · 2011-01-17 13:22:08Z

3

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

answered Jan 17, 2011 at 13:22

Vincent Malgrat

67.9k9 gold badges122 silver badges176 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Arc Over a year ago

If they are not in the same order. Like some of them are in the order Maharastra, Mumbai, India

Collectives™ on Stack Overflow

Problem with using regular expression in SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related