SQL中使用正则表达式的问题

发布于 2024-10-12 03:02:08 字数 540 浏览 2 评论 0原文

我有一个名为“数据描述”的列，其中包含类似“

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

每一行描述一个数据描述列的值”的条目。

现在我需要更新另一个表，该表将包含三列，其中

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

我将有一个在城市和城市代码之间进行映射的表。现在我需要创建另一个表，其中包含城市代码、州名称、国家/地区三列。

如何使用几个 SQl 语句而不使用任何 PL/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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

苄①跕圉湢 2024-10-19 03:02:08

您可以使用 REGEXP_SUBSTR 从源列中提取信息：

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

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

回复收藏 0 原文

~没有更多了~