SQL 从字符串中提取值

Question

如何从字符串中提取值？我正在尝试分成 3 个新列。城市、州和邮政编码的单独列。

我已经尝试过

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))

我可以使用以下代码来划分城市(西沃里克除外)，但不确定如何使其适用于州和邮政编码。这也消除了错误。

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

有什么想法可以尝试吗？

Answer 1

看起来您的邮政编码和州的长度都相同。如果这是真的，你应该能够使用这样的东西:

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;

输出: