Split Single Column into multiple and Load it to a Table or a View

Question

I'm using SQL Server 2008. I have a source table with a few columns (A, B) containing string data to split into a multiple columns. I do have function that does the split already written.

The data from the Source table (the source table format cannot be modified) is used in a View being created. But I need to have my View have already split data for Column A and B from the Source table. So, my view will have extra columns that are not in the Source table.

Then the View populated with the Source table is used to Merge with the Other Table.

There two questions here:

1. Can I split column A and B from the Source table when creating a View, but do not change the Source Table?

2. How to use my existing User Defined Function in the View "Select" statement to accomplish this task?

Idea in short:

String to split is also shown in the example in the commented out section. Pretty much have Destination table, vStandardizedData View, SP that uses the View data to Merge to tblStandardizedData table. So, in my Source column I have column A and B that I need to split before loading to tblStandardizedData table.

There are five objects that I'm working on:

1. Source File
2. Destination Table
3. vStandardizedData View
4. tblStandardizedData table
5. Stored procedure that does merge (Update and Insert) form the vStandardizedData View.

Note: all the 5 objects a listed in the order they are supposed to be created and loaded.

Separately from this there is an existing UDFunction that can split the string which I was told to use

Example of the string in column A (column B has the same format data) to be split:

6667 Mission Street, 4567 7rd Street, 65 Sully Pond Park

Desired result:

User-defined function returns a table variable:

CREATE FUNCTION [Schema].[udfStringDelimeterfromTable]
(
    @sInputList VARCHAR(MAX) -- List of delimited items
  , @Delimiter CHAR(1) = ',' -- delimiter that separates items
)   
RETURNS @List TABLE (Item VARCHAR(MAX)) WITH SCHEMABINDING
/* 
* Returns a table of strings that have been split by a delimiter.
* Similar to the Visual Basic (or VBA) SPLIT function. The 
* strings are trimmed before being returned.  Null items are not
* returned so if there are multiple separators between items, 
* only the non-null items are returned.
* Space is not a valid delimiter.
*
* Example:
SELECT * FROM [Schema].[udfStringDelimeterfromTable]('abcd,123, 456, efh,,hi', ',')
*
* Test:
DECLARE @Count INT, @Delim CHAR(10), @Input VARCHAR(128)
SELECT @Count = Count(*) 
    FROM  [Schema].[udfStringDelimeterfromTable]('abcd,123, 456', ',')
PRINT 'TEST 1 3 lines:' + CASE WHEN @Count=3 
              THEN 'Worked' ELSE 'ERROR' END
SELECT @DELIM=CHAR(10)
     , @INPUT = 'Line 1' + @delim + 'line 2' + @Delim
SELECT @Count = Count(*) 
    FROM  [Schema].[udfStringDelimeterfromTable](@Input, @Delim)
PRINT 'TEST 2  LF    :' + CASE WHEN @Count=2 
              THEN 'Worked' ELSE 'ERROR' END

Answer 1

What I'd ask you, is to read this: How to create a Minimal, Complete, and Verifiable example.

In general: If you use your UDF, you'll get table-wise data. It was best, if your UDF would return the item together with a running number. Otherwise you'll first need to use ROW_NUMBER() OVER(...) to create a part number in order to create your target column names via string concatenation. Then use PIVOT to get the columns side-by-side.

An easier approach could be a string split via XML like in this answer

A quick proof of concept to show the principles:

DECLARE @tbl TABLE(ID INT,YourValues VARCHAR(100));
INSERT INTO @tbl VALUES
 (1,'6667 Mission Street, 4567 7rd Street, 65 Sully Pond Park')
,(2,'Other addr1, one more addr, and another one, and even one more');

WITH Casted AS
(
    SELECT *
          ,CAST('<x>' + REPLACE(YourValues,',','</x><x>') + '</x>' AS XML) AS AsXml
    FROM @tbl
)
SELECT *
      ,LTRIM(RTRIM(AsXml.value('/x[1]','nvarchar(max)'))) AS Address1
      ,LTRIM(RTRIM(AsXml.value('/x[2]','nvarchar(max)'))) AS Address2
      ,LTRIM(RTRIM(AsXml.value('/x[3]','nvarchar(max)'))) AS Address3
      ,LTRIM(RTRIM(AsXml.value('/x[4]','nvarchar(max)'))) AS Address4
      ,LTRIM(RTRIM(AsXml.value('/x[5]','nvarchar(max)'))) AS Address5
FROM Casted

If your values might include forbidden characters (especially <,> and &) you can find an approach to deal with this in the linked answer.

The result

+----+---------------------+-----------------+--------------------+-------------------+----------+
| ID | Address1            | Address2        | Address3           | Address4          | Address5 |
+----+---------------------+-----------------+--------------------+-------------------+----------+
| 1  | 6667 Mission Street | 4567 7rd Street | 65 Sully Pond Park | NULL              | NULL     |
+----+---------------------+-----------------+--------------------+-------------------+----------+
| 2  | Other addr1         | one more addr   | and another one    | and even one more | NULL     |
+----+---------------------+-----------------+--------------------+-------------------+----------+