用定界符将一列分成多列(可能有也可能没有) [英] Split a column into multiple columns by a delimiter (that may or may not be there)

问题描述

因此，例如，假设我有一个包含以下列的表:id, address
我想将address列分为3列:当地址采用类似123, FakeStreet, FakeCity的格式时，number, street, city.

So suppose, for example, I have a table that has the following columns: id, address
I want to split the address column into 3 columns: number, street, city when the address is in the format like 123, FakeStreet, FakeCity.

但这是要抓住的地方！并非address列中的每个值都有一个数字或城市.其中一些只有街道名称.在这种情况下，地址看起来就像FakeStreet一样，应该在NULL中填写数字和城市.

But here's the catch! Not every value in the address column has a number or city; some of them only have the street name. In this case, the address would simply look like FakeStreet and it should fill in NULL for the number and city.

示例输入:

id     address
--------------  
1      123, fake street, fakCity  
2      31, barrington, anotherCity  
3      main street
4      25, york street, yetAnotherCity

输出:

id     num    streetName    cityName  
------------------------------------  
1      123    fake street   fakeCity
2      31     barrington    anotherCity  
3      NULL   main street   NULL
4      25     york street   yetAnotherCity

此外，我可以假设该地址将仅包含街道名称，也可能包含整个地址.

Also, I can assume that the address will either have only the street name, or the entire address.

有没有一种方法可以使用SQL或PL SQL做到这一点?否则，我想我将不得不将其拆分为两个单独的查询，在sql之外进行修改，然后将两个查询的结果放回一起.我想再做些...紧凑，以免缺少更好的用语.

Is there a way I can do this using SQL or PL SQL? Else, I'm thinking I'm going to have to split this into two separate queries, modify them outside of sql, then put the results of the two queries back together. I'd like to have something a little more... compact for a lack of a better term.

啊，我也想提一提，我只需要将拆分列作为SELECT的结果.我不打算实际修改表结构.

Ah, I'd also like to mention that I only need the split columns as the result of a SELECT. I'm not looking to actually modify the table structure.

谢谢.

推荐答案

由于需要遵守规则，因此需要有条件地中断字符串I can assume that the address will either have only the street name, or the entire address.您需要的查询是以下内容:

You need to break the string conditionaly as you have your rule I can assume that the address will either have only the street name, or the entire address. the query you need is this one:

select
id,
case when instr(address,',') >= 1
   then REGEXP_SUBSTR(address, '[^,]+', 1, 1) 
   else null end num,
case when instr(address,',') >= 1
   then REGEXP_SUBSTR(address, '[^,]+', 1, 2) 
   else address end street,
case when instr(address,',') >= 1
   then REGEXP_SUBSTR(address, '[^,]+', 1, 3) 
   else null end city
from ads

此函数REGEXP_SUBSTR(address, '[^,]+', 1, 1)会根据正则表达式[^,]+从您的列中获取一个子字符串，这意味着不是,的任何内容，第一个1是该函数将评估的起始位置字段address和第二个1正则表达式的N出现.

This function REGEXP_SUBSTR(address, '[^,]+', 1, 1) it is getting a substring from your column based on a regular expression [^,]+ which mean anything that is not a , the first 1 is the start position that the function will evaluate of the field address and the second 1 the N occurency of the regular expression.

在小提琴上查看它: http://sqlfiddle.com/#!4/dd1901/8

这篇关于用定界符将一列分成多列(可能有也可能没有)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！