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
   

SQL Server - Fun With Dates

So, before anyone complains about my method of posting functions.  I like functions.  I work well with them.

But, hey.  Not everyone likes them.  You just want the code.  If you want a somewhat detailed explanation of what is going on, visit their related posts.

So, below is where I'll keep the simple code snippets, with just an intro explanation...

I'll add to this as I add more posts relating to dates or even before I create the related posts.
I'll try and remember to do the same with the various other items I work with.

Oh, and these may not be the fastest queries or the most readable.  But they should work.  If you find one that doesn't let me know.

Enjoy...


-- Set the Date you plan on using
DECLARE @Date DateTime
SET     @Date = '2011-12-14'


-- Get the Last Day of the Month
SELECT DATEADD(d,-DAY(DATEADD(m,1,@Date)),DATEADD(m,1,@date))

-- Get the Last Day of the Prior Month
SELECT DATEADD(d,-Day(@Date),@Date) 

-- Get the First Day of the Month
SELECT DATEADD(d,-Day(@Date) +1 ,@Date)

-- Get the First Day of the prior Month
SELECT DATEADD(d,-DAY(DATEADD(m,-1,@Date)-1 ),DATEADD(m,-1,@date)) 

-- Get the First Day of the next Month
SELECT DATEADD(d,-DAY(DATEADD(m,1,@Date)-1 ),DATEADD(m,1,@date))

--Get the Last Day of the Week
SELECT DATEADD(d,7-(DATEPART(dw,@Date)),@Date)

--Get the First Day of the Given Week
SELECT DATEADD(d,-(DATEPART(dw, @Date) - 1),@Date) 


-- The following will return only the date portion of the 
-- DateTime.  This strips off the Time and leaves only zeros.
SET @Date = GETDATE() --Get current system date and time


SELECT  @Date                            as OriginalDate
      , DATEADD(d,0,DATEDIFF(d,0,@Date)) as StrippedDate

SQL Server - Get Last Day of a given Month

This function will return the last day of a given month based upon the date passed in. 


-------------------------------------------------
--Create Function to return the last day of the
--week based upon the date passed in.
--
--You pass in a Date and it return
--the Date for last day of the week
-------------------------------------------------
CREATE FUNCTION [dbo].[xGetLastDayOfMonth](@Date DateTime)
RETURNS DateTime
AS

BEGIN

    DECLARE @DayOfMonth        INT
    DECLARE @AddOneMonthToDate DateTime

    -- Add one month to the date passed in
    SET @AddOneMonthToDate = DateAdd(m,1,@Date)

    -- Get the day number of @AddOneMonthToDate
        -- When adding one Month to the passed in Date
        -- we could end up with a different day than
        -- the original month.  Add one month to 1/31/2011
        -- and we get 2/28/2011.  We want to make sure
        -- we get day number in the next month.
    SET @DayOfMonth = DAY(@AddOneMonthToDate)



    -- We will now subtract the @DayOfMonth
    -- from the @AddOneMonthToDate.  Since there is no
    -- such thing as a month with a ZERO, it rolls
    -- back to the last day of the prior month.
   
    -- if you were to use 1/29/2000,1/30/2000 or 1/31/2000
    -- as the date passed in an we add one month
    -- to that we would get 2/28/2000 back.  Since
    -- we told it to add one month, it only adds one
    -- month to and makes sure we get back a valid
    -- date.
  
    -- The @dayOfMonth that we then get from this
    -- is 28.
   
    -- We then subtract 28 from 2/28/2000.
   
    -- Since we can't have 2/00/2000 it goes to the
    -- prior day and the result in 1/31/2000.
    RETURN DATEADD(d, -@DayOfMonth, @AddOneMonthToDate)
      

    -- Of course we could just use this one line below
    -- to do all the work for us, instead of the previous
    -- five lines.  But then that wouldn't be fun, eh?
    /*
    RETURN DATEADD(d
                     , -DAY(  DATEADD(m,1,@date)  )
                     , DATEADD(m,1,@date)
                  )           
    */           

END

GO

-------------------------------------------------
--Now we will check to see if the code works
-------------------------------------------------

-- Should return 12/31/2011 
Select dbo.xGetLastDayOfMonth('2011-12-25')








 

SQL Server - Get the Last day of a given week

So how do we find the last day of the week for a given date?

-------------------------------------------------
-- Does the function already exist?
-- if so... drop it.
-------------------------------------------------
IF OBJECT_ID(N'dbo.xGetLastDayOfWeek',N'FN') IS NOT NULL
BEGIN
    print 'Function found... Dropping'
    DROP FUNCTION dbo.xGetLastDayOfWeek
END

GO

CREATE FUNCTION dbo.xGetLastDayOfWeek(@Date DateTime)
RETURNS DateTime
AS
-------------------------------------------------
--Function will return the last day of the
--week based upon the date passed in.
--
--You pass in a Date and it return
--the Date for last day of the week
-------------------------------------------------


BEGIN

    DECLARE @DayOfWeek      INT
    DECLARE @DaysToAdd      INT

    -- Get the day of the week
    -- i.e. if today is Thursday and the week starts on Sunday
    --      then the day of the week is 5
    SET @DayOfWeek = DatePart( dw, @Date )
   
   
    -- We need to subtract the @DayOfWeek from 7.
    -- This will tell us how many days we need to add to the
    -- current Date to find the end of the week.
    SET @DaysToAdd = 7 - @DayOfWeek


    -- Now we add @@DaysToAdd to the @date passed in.
    -- This will return the last day of the week for the given @date.
    RETURN DATEADD( d, @DaysToAdd, @Date )


    -- Instead of the previous five lines of code we could have also
    -- used this single line.  I broke it out above to hopefully better
    -- explain what was happening.

    --RETURN DATEADD(d,7-(DATEPART(dw, @Date) ),@Date)
END

GO 

-------------------------------------------------
--Now we will check to see if the code works
-------------------------------------------------

 
-- Should return 12/03/2011
Select dbo.xGetLastDayOfWeek('2011-12-01')


-- Should return 12/31/2011  
Select dbo.xGetLastDayOfWeek('2011-12-25')

 
-- Should return 12/31/2011 
Select dbo.xGetLastDayOfWeek('2011-12-31')

SQL Server - Get the First day of a given week

The first of several Date functions that I've created to determine certain dates.

This one will return the first day of a week based upon the date you pass in.

-------------------------------------------------
-- Does the function already exist?
-- if so... drop it.
-------------------------------------------------
IF OBJECT_ID(N'dbo.xGetFirstDayOfWeek',N'FN') IS NOT NULL
BEGIN
    print 'Function found... Dropping'
    DROP FUNCTION dbo.xGetFirstDayOfWeek
END

GO

CREATE FUNCTION dbo.xGetFirstDayOfWeek(@Date DateTime)
RETURNS DateTime
AS





-------------------------------------------------
--Function will return the first day of the
--week based upon the date passed in.
--
--You pass in a Date and it return
--the Date for first day of the week
-------------------------------------------------
BEGIN

    DECLARE @DayOfWeek      INT
    DECLARE @DaysToSubtract INT

    -- Get the day of the week
    -- i.e. if today is Thursday and the week starts on Sunday
    --      then the day of the week is 5
    SET @DayOfWeek = DatePart( dw, @Date )
   
   
   -- We need to subtract one from the @DayOfWeek.
   -- The reason is that if today is the 5th day and we want
   -- to get back to the 1st day, we only need to subtract 4
   -- from the Date.  Otherwise we would end up at 0.
   SET @DaysToSubtract = @DayOfWeek -1


   -- Now we subtract @DaysToSubtract from the @date passed in.
   -- This will return the first day of the week for the given @date.
    RETURN DATEADD( d, -@DaysToSubtract, @Date )


    -- Instead of the previous five lines of code we could 
    -- have also used this single line.  I broke it out above to
    -- hopefully better explain what was happening.
 
    -- RETURN DATEADD(d,-(DATEPART(dw, @Date) - 1),@Date)
END

GO

-------------------------------------------------
--Now we will check to see if the code works
-------------------------------------------------

-- Should return 11/27/2011

Select dbo.xGetFirstDayOfWeek('2011-12-01')

 

-- Should return 12/25/2011 --already the first day of the week
Select dbo.xGetFirstDayOfWeek('2011-12-25')