SQL从字符串中提取值

问题描述：

如何从字符串中提取值？我试图分成3个新的列。城市，州和邮编的单独列。SQL从字符串中提取值

我已经试过

select address2, 
left(address2, charindex('',address2)-1) 
from table

和---当我尝试下面的代码，我得到“传递给左或子功能无效的长度参数”

,LTRIM(substring(a.Address2, CHARINDEX(' ', a.Address2)+1, CHARINDEX(' ', substring(a.address2, charindex(' ', 
a.address2)+1, len(a.address2)))-1))

我可以打出来这个城市（West Warwick除外）使用下面的代码，但不知道如何使它适用于状态和zip。这也消除了错误。

SUBSTRING(Address2,1,CHARINDEX(' ', a.address2+ ' ')-1) as city

任何想法要尝试什么？

答

它看起来像你的邮政编码和您的国家都是一样的长度。如果这是真的，你应该能够使用这样的事情：

SELECT 
    LEFT(a.Address2,LEN(a.Address2) - 13) AS City, 
    RIGHT(LEFT(a.Address2,LEN(a.Address2) - 11),2) AS State, 
    RIGHT(a.Address2,10) AS Zip_Code 
FROM 
    table;

演示代码

创建表和数据：

CREATE TABLE MyTable (Address2 VARCHAR(100)); 

INSERT INTO MyTable 
VALUES 
    ('SAN DIEGO CA 92128-1234'), 
    ('WEST WARWICK RI 02893-1349'), 
    ('RICHMOND IN 47374-9409');

查询：

SELECT 
    LEFT(Address2,LEN(Address2) - 13) AS City, 
    RIGHT(LEFT(Address2,LEN(Address2) - 11),2) AS State, 
    RIGHT(Address2,10) AS Zip_Code 
FROM 
    MyTable;

输出：

我必须有where子句吗？我仍然收到“传递给LEFT或SUBSTRING函数的无效长度参数”。 – BIReportGuy

我确实更新了'State'逻辑 - 我被关闭了一个。如果仍然出现错误，那就意味着有一些记录'state'或'zip'（或两者）的长度与您提供的示例数据中的长度不相同。 – Nicarus

这似乎消除了错误，但我只知道如何使它适用于一列。（West Warwick除外）SUBSTRING（Address2,1，CHARINDEX（''，address2 +''）-1）作为城市 – BIReportGuy

答

既然你只有3件（城市/州/邮编），你可以采取在SQL Server 2008及更高版本称为parsename功能的优势。（该功能的初衷是解析出对象名称。）

使用replace和parsename函数的组合可以使您能够将数据分成3部分，即使状态长度（不太可能）或Zip（更可能）更改。

实施例的数据：

create table #my_table 
    (
     address2 varchar(75) not null 
    ) 

insert into #my_table values ('CONNERSVILLE IN 47331-3351') 
insert into #my_table values ('WEST WARWICK RI 02893-1349') 
insert into #my_table values ('RICHMOND IN 47374-9409') 
insert into #my_table values ('WILLIAMSBURG IN 47393-9617') 
insert into #my_table values ('FARMERSVILLE OH 45325-9226') 
--this record is an example of a likely scenario for when the zip length would change. 
insert into #my_table values ('WILLIAMSBURG IN 47393')

解决方案：

with len_vals as 
    (
     select t.address2 
     , len(parsename(replace(t.address2,' ','.'), 1)) as zip_len 
     , len(parsename(replace(t.address2,' ','.'), 2)) as st_len 
     from #my_table as t 
     group by t.address2 
    ) 
select left(a.address2, len(a.address2) - b.zip_len - b.st_len - 2) as city 
, substring(a.address2, len(a.address2) - b.zip_len - 2, b.st_len) as st 
, right(a.address2, b.zip_len) as zip_code 
from #my_table as a 
inner join len_vals as b on a.address2 = b.address2

结果：

SQL从字符串中提取值

相关推荐