SQL Server - Split a Text String into a table

So, I work for a car insurance company.  I do lots of ETL work.  ETL = Extract Transform and Load.

I regularly receive lists of vehicles.  These lists are not formatted nicely.
 
Generally, all the vehicle information for that vehicle is in one column.  But that doesn't really work to well for me.  I need to separate the Year, Make and Model out into their own columns.

This function has been one of the most useful functions I have run across for this specific task.  I'll pass in the vehicle information as the string input and it will return a table full of rows, one for each item in the vehicle.  It does this based upon the delimiter passed in as the second variable.

I can then use that result set to properly place the information into their appropriate columns in my tables.



-------------------------------------------------
-- Does the function already exist?
-- if so... drop it.
-------------------------------------------------
IF OBJECT_ID(N'dbo.xSplitTextStringIntoTable',N'TF') IS NOT NULL    
BEGIN
    PRINT 'Function Found... Dropping'
    DROP FUNCTION [dbo].[xSplitTextStringIntoTable]
END
GO


--Create the function
CREATE FUNCTION [dbo].[xSplitTextStringIntoTable]
(   
    @Input     NVARCHAR(MAX)
  , @Delimiter NVARCHAR(MAX)
)
RETURNS @Return TABLE
(
        ID    INT IDENTITY(1,1)
    ,   Value NVARCHAR(MAX)
)
AS
---------------------------------------------------------------------------
-- Receives:
--   @Input
--          :The string of values we wish to break up
--   @Delimiter
--          :tells how text is to be split 
--           usually a space or comma, I really like the Pipe 
--           delimiter '|'
--           
--
--    Returned: Table structure with each delimited word returned as a row
--              i.e. '1999 Dodge Viper'
--              returns 3 rows; (1999, Dodge, Viper ) each with its own row
---------------------------------------------------------------------------
BEGIN

    DECLARE @Iterator   INT
    DECLARE @Index      INT
   
    -- We are going to iterate through each item passed in.
    -- An item is based upon the delimiter used
    SET @Iterator = 1
   
    -- Get the index of the first item
    SET @Index = CHARINDEX( @Delimiter, @Input )

    --If there are more than one item it will go through this loop
    --IF NOT it will skip the while loop
    WHILE ( @Index > 0 )

    BEGIN

        -- Insert the item into our table 
        INSERT INTO @Return ( Value )
            SELECT
                Value = LTRIM(RTRIM(
                            SUBSTRING(@Input, 1, @Index - 1)
                              ))

        -- Now remove item in the @Input string since we have
        -- added it to the table
        SET @Input = SUBSTRING(
                                  @Input
                                , @Index + DATALENGTH(@Delimiter) / 2
                                , LEN( @Input )
                               )

        -- increment the iterator by one
        SET @Iterator = @Iterator + 1

        -- Get the next index number, if none found while loop will end
        SET @Index    = CHARINDEX( @Delimiter, @Input )
    END  -- END OF THE WHILE LOOP
   
    -- Here we either
    --     enter the lone item passed in
    --     or the last item passed in
    INSERT INTO @Return (Value)
        SELECT Value = LTRIM(RTRIM( @Input ))

    RETURN
END

GO




-------------------------------------------------
--Now we will check to see if the code works
-------------------------------------------------
DECLARE @Vehicle NVARCHAR(MAX)
SELECT @Vehicle = '1964 Dodge Dakato Station Wagon'


-- Get the full table of values
SELECT *
    FROM   [dbo].[xSplitTextStringIntoTable] (@Vehicle, ' ')


-- Or select from it just like a regular table
-- In this case I just want the value of the 3rd item in the list
SELECT Value
    FROM   [dbo].[xSplitTextStringIntoTable] (@Vehicle, ' ')
    Where ID = 3