SQL - re.split:先用逗号，再用空格

Question

我想将以下字符串拆分为市、省、邮政编码。非常感谢你的帮助！

说明:逗号拆分，空格拆分一次。

A = 'Vaughan, ON L6D 9X0'

结果：

(Vaughan, ON, L6D9X0)

尝试：

re.split(',|/s[1]', A)

Answer 1

如果您使用的是Microsoft SQL Server，那么可以使用SUBSTRING() &CHARINDEX()函数查找特定的Char索引来拆分string值。

SELECT SUBSTRING(<column>, 1, CHARINDEX(',', <column>)-1) City,
       SUBSTRING(SUBSTRING(<column>, CHARINDEX(' ', <column>)+1, LEN(<column>)), 1, CHARINDEX(' ', SUBSTRING(<column>, CHARINDEX(' ', <column>)+1, LEN(<column>)))) Province,
       SUBSTRING(SUBSTRING(<column>, CHARINDEX(' ', <column>)+1, LEN(<column>)), CHARINDEX(' ', SUBSTRING(<column>, CHARINDEX(' ', <column>)+1, LEN(<column>)))+1, LEN(<column>)) [Postal Code];

所需输出:

City     Province    Postal Code
Vaughan  ON          L6D 9X0

Answer 2

尝尝这个,

这选择了行中的值，也许你可以用pivot the cte C2来获得它作为列

WITH CTE
AS
(
  SELECT
    1 Seq,
    'Vaughan, ON L6D 9X0' "Txt"

  UNION ALL

  SELECT
    Seq+1 "Seq",
    Txt = CASE WHEN Txt LIKE '% %'
              THEN LTRIM(RTRIM(SUBSTRING(Txt,CHARINDEX(' ',Txt),LEN(Txt))))
            ELSE NULL END
    FROM CTE
      WHERE ISNULL(txt,'')<>''
),C2
AS
(
SELECT
  CASE Seq
    WHEN 1 THEN 'City'
    WHEN 2 THEN 'Province'
    ELSE 'PostalCode' END "Head",
  CASE Seq
    WHEN 1
      THEN SUBSTRING(Txt,1,CHARINDEX(',',Txt)-1)
    WHEN 2 
      THEN SUBSTRING(Txt,1,CHARINDEX(' ',Txt)-1)
    ELSE Txt END "Txt"
  FROM CTE
    WHERE Seq<4
)
SELECT
  *
  FROM C2;

如果您有多个行需要以相同的方式进行解析，那么您可以在第一个CTE的select语句中提供相同的解析，在这种情况下，第一个select上的Seq的逻辑可能需要更改。如上所述，输出将如下所示

​

​

Answer 3

使用Postgres，您可以使用以下命令完成此操作：

select split_part(a, ',', 1) as city,
       left(trim(split_part(a,',',2)), strpos(trim(split_part(a,',',2)), ' ')),
       substr(trim(split_part(a,',',2)) as province, strpos(trim(split_part(a,',',2)), ' ') + 1) as postal_code
from the_table;

通过使用派生表，可以使其更具可读性：

select city,
       left(second_part, strpos(second_part, ' ')) as province,
       substr(second_part, strpos(second_part, ' ') + 1) as postal_code
from (
  select split_part(a, ',', 1) as city,
         trim(split_part(a, ',', 2)) as second_part
  from the_table
) t