Use Split String Table Value Function in a Select statement

Question

I have a table from a third party source that I have no control over. Each address field contains the FULL address including postcode. Each address has a dynamic number of separate address fields included. I have got a Split() TVF that works in splitting the address fields into seperate rows. However, when I CROSS APPLY the TVF table back to the SELECT query it returns a row for each ro in the TVF table. How do I get it to return one row for the main table and separate COLUMNS from the rows in the TVF table?

Example of addresses supplied:

a. 1 The Street, The Locality, The Town, The County, The Postcode

b. 2 The Building, The Street, The Town, The Postcode

c. Floor 3, 3 The Street, The Locality, The Town, The County, The Postcode

The TVF returns these as one value per row using the ',' as a delimiter. I then need to join that data back to the original data as one column per address field.

This is my SELECT QUERY:

select DISTINCT TOP 5 ttp.ProjectID
  ,cac.ID
  ,cac1.Proprietor1Address1
  --,CASE WHEN addr.ID = 1 THEN addr.Data END AS Address1
FROM ArdentTest.ardent.LRTitlesToProcess ttp

JOIN LandRegistryData.landreg.CommercialandCorporateOwnershipData cac
    ON ttp.TitleNo = cac.TitleNumber

JOIN (SELECT TitleNumber
            ,Proprietor1Address1
       FROM LandRegistryData.landreg.CommercialandCorporateOwnershipData
       WHERE 1 = 1
            AND ISNULL(Proprietor1Address1, '') <> '') cac1
    ON ttp.TitleNo = cac1.TitleNumber

CROSS APPLY DBAdmin.resource.Split(cac1.Proprietor1Address1, ', ') addr

WHERE 1 = 1
AND ttp.DateLRRequestSent IS NULL
AND cac.ID IN (50764, 78800, 157089, 206049, 449112)
ORDER BY 1

Which produces the following results:

ProjectID   ID      Proprietor1Address1
1010        50764   Bridge House, 1 Walnut Tree Close, Guildford, Surrey GU1 4LZ
1010        78800   Bridge House, 1 Walnut Tree Close, Guildford, Surrey GU1 4LZ
1010        157089  Bridge House, 1 Walnut Tree Close, Guildford, Surrey GU1 4LZ
1010        206049  Bridge House, 1 Walnut Tree Close, Guildford, Surrey GU1 4LZ
1010        449112  Church House, Great Smith Street, London SW1P 3AZ

I need to use the rows from the function to add separate address columns to the result set and I cannot figure out how to do it.

You cannot write a query that will return an *unknown* number of columns. Every query always produces a result set with a fixed "shape" - the number of columns, their names and their types. — Damien_The_Unbeliever, Jul 24 '18 at 08:41
@Damien_The_Unbeliever except when using [dynamic pivot...](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Zohar Peled, Jul 24 '18 at 08:44
@ZoharPeled - but I would argue that is because when you're using dynamic SQL, you're constructing a *new* query to execute. Of course, that new query may have any shape you choose. It doesn't change the basic premise that any *particular* query gives results with a fixed shape. — Damien_The_Unbeliever, Jul 24 '18 at 08:46
@Damien_The_Unbeliever I accept your claim. Still, a dynamic pivot can help the OP get the desired output. — Zohar Peled, Jul 24 '18 at 08:51
I may not have been particularly clear in my original question. The use of the term DYNAMIC doesnot apply to the number of rows or columns in a table. It applies to the number of address fields contained within a SINGLE column from the external data source. I need to SPLIT this SINGLE column into different address columns to format it correctly for the database it is going into. — Allen Jones, Jul 24 '18 at 09:00
You should provide sample data and expected result. It doesn't have to be your data, just a minimal working example that reproduces the issue. — Rigerta, Jul 24 '18 at 09:04

Serkan Arslan · Accepted Answer · 2018-07-24T09:22:42.830

If you can make some assumption like an address can contain max 10 parts. You can use this script. I used STRING_SPLIT instead of your split function.

DECLARE @Table TABLE (Id INT, Address VARCHAR(500))
INSERT INTO @Table VALUES
(1, '1 The Street, The Locality, The Town, The County, The Postcode'),
(2, '2 The Building, The Street, The Town, The Postcode'),
(3, 'Floor 3, 3 The Street, The Locality, The Town, The County, The Postcode')

SELECT * FROM @Table T
CROSS APPLY(
    SELECT * FROM (SELECT ROW_NUMBER()OVER(ORDER BY (SELECT NULL)) RN, * FROM STRING_SPLIT(T.Address, ','))  SRC
    PIVOT (MAX(value) FOR RN IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) PVT
) X

Result:

Id          Address                                                                          1                     2                     3                     4                     5                     6                     7                     8                     9                     10
----------- -------------------------------------------------------------------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- ---------------------
1           1 The Street, The Locality, The Town, The County, The Postcode                   1 The Street           The Locality          The Town              The County            The Postcode         NULL                  NULL                  NULL                  NULL                  NULL
2           2 The Building, The Street, The Town, The Postcode                               2 The Building         The Street            The Town              The Postcode         NULL                  NULL                  NULL                  NULL                  NULL                  NULL
3           Floor 3, 3 The Street, The Locality, The Town, The County, The Postcode          Floor 3                3 The Street          The Locality          The Town              The County            The Postcode         NULL                  NULL                  NULL                  NULL

STRING_SPLIT is new in SQL 2016. Unfortunately we have not upgraded yet. Still on SQL 2014 :-( — Allen Jones, Jul 24 '18 at 09:24
I used STRING_SPLIT for test purpose, you can use your own split function instead of it. — Serkan Arslan, Jul 24 '18 at 09:28
One problem with this approach is ,that `STRING_SPLIT` does not guarantee to return the elements in the given order... There are alternatives returning the parts together with an index (e.g. Jeff Moden's `DelimitedSplit8K`) — Shnugo, Jul 24 '18 at 09:53

Shnugo · Answer 2 · 2018-07-24T09:49:28.007

You can create an XML out of your concatenated string and address each part via index

--the table (thx Serkan, I copied your DDL)

DECLARE @Table TABLE (Id INT, Address VARCHAR(500))
INSERT INTO @Table VALUES
(1, '1 The Street, The Locality, The Town, The County, The Postcode'),
(2, '2 The Building, The Street, The Town, The Postcode'),
(3, 'Floor 3, 3 The Street, The Locality, The Town, The County, The Postcode');

--This query will return the parts one by one

WITH Splitted AS
(
    SELECT *
          ,CAST('<x>' + REPLACE((SELECT [Address] AS [*] FOR XML PATH('')),',','</x><x>') + '</x>' AS XML) AsXml
    FROM @Table
)
SELECT *
      ,AsXml.value('/x[1]','nvarchar(max)') AS Col1 
      ,AsXml.value('/x[2]','nvarchar(max)') AS Col2 
      ,AsXml.value('/x[3]','nvarchar(max)') AS Col3 
      ,AsXml.value('/x[4]','nvarchar(max)') AS Col4 
      ,AsXml.value('/x[5]','nvarchar(max)') AS Col5 
      ,AsXml.value('/x[6]','nvarchar(max)') AS Col6 
FROM Splitted

--the result

/*+----+----------------+--------------+--------------+--------------+--------------+--------------+
| ID | Col1           | Col2         | Col3         | Col4         | Col5         | Col6         |
+----+----------------+--------------+--------------+--------------+--------------+--------------+
| 1  | 1 The Street   | The Locality | The Town     | The County   | The Postcode | NULL         |
+----+----------------+--------------+--------------+--------------+--------------+--------------+
| 2  | 2 The Building | The Street   | The Town     | The Postcode | NULL         | NULL         |
+----+----------------+--------------+--------------+--------------+--------------+--------------+
| 3  | Floor 3        | 3 The Street | The Locality | The Town     | The County   | The Postcode |
+----+----------------+--------------+--------------+--------------+--------------+--------------+*/

--You might use REVERSE on all steps. This will bring the Postcode in the first place in all cases (as long as the postcode is the last element there)

WITH Splitted AS
(
    SELECT *
          ,CAST('<x>' + REPLACE((SELECT REVERSE([Address]) AS [*] FOR XML PATH('')),',','</x><x>') + '</x>' AS XML) AsXml
    FROM @Table
)
SELECT *
      ,REVERSE(AsXml.value('/x[1]','nvarchar(max)')) AS Col1 
      ,REVERSE(AsXml.value('/x[2]','nvarchar(max)')) AS Col2 
      ,REVERSE(AsXml.value('/x[3]','nvarchar(max)')) AS Col3 
      ,REVERSE(AsXml.value('/x[4]','nvarchar(max)')) AS Col4 
      ,REVERSE(AsXml.value('/x[5]','nvarchar(max)')) AS Col5 
      ,REVERSE(AsXml.value('/x[6]','nvarchar(max)')) AS Col6 
FROM Splitted;

--the result

/*+----+--------------+------------+------------+----------------+--------------+---------+
| ID | Col1         | Col2       | Col3       | Col4           | Col5         | Col6    |
+----+--------------+------------+------------+----------------+--------------+---------+
| 1  | The Postcode | The County | The Town   | The Locality   | 1 The Street | NULL    |
+----+--------------+------------+------------+----------------+--------------+---------+
| 2  | The Postcode | The Town   | The Street | 2 The Building | NULL         | NULL    |
+----+--------------+------------+------------+----------------+--------------+---------+
| 3  | The Postcode | The County | The Town   | The Locality   | 3 The Street | Floor 3 |
+----+--------------+------------+------------+----------------+--------------+---------+*/

Use Split String Table Value Function in a Select statement

2 Answers2