splitting a string by multiple delimitters

sql, sql-server, sql-server-2008, t-sql

Solution

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

I used `PatIndex()` so an address like "Sterling Rd" won't give you a false match on "Ste"

Result set:

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

Problem

I have a set of addresses: ``` 34 Main St Suite 23 435 Center Road Ste 3 34 Jack Corner Bldg 4 2 Some Street Building 345 ``` the delimitters would be: ``` Suite, Ste, Bldg, Building ``` I would like to separate these addresses into `address1` and `address2` like this: ``` +---------------------+--------------+ | Address1 | Address2 | +---------------------+--------------+ | 34 Main St | Suite 23 | | 435 Center Road | Ste 3 | | 34 Jack Corner | Bldg 4 | | 2 Some Street | Building 345 | +---------------------+--------------+ ``` How can I define a set of delimitters and delimit in this fashion?

Original source