sql - 用多个分隔符分割字符串

Question

我有一组地址:

34 Main St Suite 23
435 Center Road Ste 3
34 Jack Corner Bldg 4
2 Some Street Building 345

分隔符为:

Suite, Ste, Bldg, Building

我想将这些地址分成 address1 和 address2，如下所示:

+---------------------+--------------+
|      Address1       |   Address2   |
+---------------------+--------------+
| 34 Main St          | Suite 23     |
| 435 Center Road     | Ste 3        |
| 34 Jack Corner      | Bldg 4       |
| 2 Some Street       | Building 345 |
+---------------------+--------------+

如何定义一组分隔符并以这种方式进行分隔？

Answer 1

SELECT
   T.Address,
   Left(T.Address, IsNull(X.Pos - 1, 2147483647)) Address1,
   Substring(T.Address, X.Pos + 1, 2147483647) Address2 -- Null if no second
FROM
   (
      VALUES
      ('34 Main St Suite 23'),
      ('435 Center Road Ste 3'),
      ('34 Jack Corner Bldg 4'),
      ('2 Some Street Building 345'),
      ('123 Sterling Rd'),
      ('405 29th St Bldg 4 Ste 217')
   ) T (Address)
   OUTER APPLY (
      SELECT TOP 1 NullIf(PatIndex(Delimiter, T.Address), 0) Pos
      FROM (
         VALUES ('% Suite %'), ('% Ste %'), ('% Bldg %'), ('% Building %')
      ) X (Delimiter)
      WHERE T.Address LIKE X.Delimiter 
      ORDER BY Pos
   ) X

我使用了 PatIndex()，因此像“Sterling Rd”这样的地址不会为您提供“Ste”的错误匹配

结果集:

Address1         Address2
---------------  --------
34 Main St       Suite 23
435 Center Road  Ste 3
34 Jack Corner   Bldg 4
2 Some Street    Building 345
123 Sterling Rd  NULL
405 29th St      Bldg 4 Ste 217