如何在SQL中解析出具有多个值的列

我在名为[City_St_Zip]的sql服务器表中有一列，其中包含如下所示的记录

  

德克萨斯州达拉斯12345

我想做的是将该列分为三个不同的列（即城市，州和邮政编码）
像这样：

  

达拉斯

  
  

    

TX

    
    

      

12345

    

  

我不确定如何在SQL中进行此操作

我尝试了以下

DeclARE @X NVARCHAR(100),DeclARE @T NVARCHAR(100),SELECT
@X = [City_St_Zip],@T = [NewDivision]
FROM
dbo.Invoice
CROSS APPLY STRING_SPLIT(@X,',');


这产生了0个结果，所以我可以肯定我做错了

有什么建议吗？我正在使用SQL Server 2019

编辑：
我也尝试过这个更接近我想要的东西

SELECT
value
FROM
dbo.Invoice
CROSS APPLY STRING_SPLIT([City_St_Zip],');


这给了我一个结果集：

  

达拉斯

  
  

    

TX 12345

  

所以我想这很麻烦，需要逗号和空格定界符。我可以将值输入另一个STRING_SPLIT吗？

SQL Server具有较差的字符串处理支持。并且，不能保证string_split()将值保持顺序。字符串搜索很危险-想想纽约，纽约。

因此，一种蛮力方法：

select left(col,charindex(',',col) - 1) as city,substring(col,col) + 2,2) as state,right(col,5) as zipcode


Here是db 小提琴。

*已根据SQL Server *

create table ctry
(
city_st_zip nvarchar(100)
);
insert into ctry values('Dallas,TX 12345');


-使用SQL-

SELECT
LEFT([city_st_zip],CHARINDEX(',[city_st_zip]) - 1) AS [City],SUBSTRING([city_st_zip],[city_st_zip]) + 2,2) as [State],RIGHT([city_st_zip],CHARINDEX(' ',[city_st_zip]) - 2) AS [Zip]
FROM ctry;


-结果-

City State Zip
Dallas TX 12345

,

如果您想使用STRING_SPLIT，则可以在没有变量的情况下使用。

样本数据：

create table dbo.Invoice
(
id int identity(1,1) primary key,[City_St_Zip] nvarchar(100)
);
insert into dbo.Invoice
([City_St_Zip]) values
('Dallas,TX 12345'),('Fort Worth,TX 12345')
GO


2 rows affected

查询：

SELECT inv.*,a.*
FROM dbo.Invoice inv
OUTER APPLY
(
SELECT
[1] AS [City],LTRIM(LEFT([2],3)) AS [State],TRIM(SUBSTRING([2],4,LEN([2]))) AS [Zip]
FROM
( SELECT spl.value,ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS rn
FROM STRING_SPLIT(inv.[City_St_Zip],') spl
) s
PIVOT (MAX(value) FOR rn IN ([1],[2])) p
) a;


结果：

id | City_St_Zip | City | State | Zip
-: | :------------------- | :--------- | :---- | :----
1 | Dallas,TX 12345 | Dallas | TX | 12345
2 | Fort Worth,TX 12345 | Fort Worth | TX | 12345

db 提琴here

其他：

使用XML类型，此SQL还可以在Sql Server 2012等早期版本中使用。

SELECT inv.*,a.City,RTRIM(LEFT(a.StateZip,a.StateZip))) AS State,LTRIM(SUBSTRING(a.StateZip,a.StateZip),LEN(a.StateZip))) AS Zip
FROM dbo.Invoice inv
OUTER APPLY
(
SELECT X.x AS CityStateZipXml,X.x.value('/x[1]','nvarchar(max)') AS City,LTRIM(X.x.value('/x[2]','nvarchar(max)')) AS StateZip
FROM (
SELECT CAST(CONCAT('',REPLACE(inv.[City_St_Zip],''),'') AS XML)
) AS X(x)
) a;