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')

SQL Server - Check for Leap Year (Part 2)

And here is the second part to check for leap year. This uses the standard algorithm to check for leap year.

For more information about Leap Year: Leap Year on Wikipedia

 

-------------------------------------------------
-- Does the function already exist? 
-- if so... drop it.
-------------------------------------------------

IF OBJECT_ID(N'dbo.xIsLeapYear2',N'FN') IS NOT NULL
BEGIN
    print 'Function found... Dropping'
    DROP FUNCTION dbo.xIsLeapYear2
END

GO

CREATE FUNCTION xIsLeapYear2(@Year SmallInt)
RETURNS CHAR(3)
AS


-------------------------------------------------
--Function will Check to See if the year
--passed in is a leap year.
--
--This function uses an Algorithm to get the job done. 
--
--You pass in a Year and it returns 'Yes' or 'No'
-------------------------------------------------
BEGIN

    DECLARE @CheckYear Char(3)
   
    SET @CheckYear =
            (
              SELECT
                CASE
                    -- Is the Year Divisible by 400 with 0 remaining?
                    WHEN @Year%400 = 0 THEN 'YES'
                   
                    -- Is the Year Divisible by 100 with 0 remaining?
                    WHEN @Year%100 = 0 THEN 'NO'
                   
                    -- Is the Year Divisible by 4   with 0 remaining?
                    WHEN @Year%4   = 0 THEN 'YES'
                ELSE
                    'NO'
                END
            )


 

    RETURN @CheckYear

END

GO

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

PRINT''
PRINT 'Using the algorithm operator to check leap year.'
PRINT '------------------------------------------------'
PRINT 'Is 1900 a Leap year?  ' + dbo.xIsLeapYear2(1900)
PRINT 'Is 2000 a Leap year?  ' + dbo.xIsLeapYear2(2000)
PRINT 'Is 2008 a Leap year?  ' + dbo.xIsLeapYear2(2008)
PRINT 'Is 2010 a Leap year?  ' + dbo.xIsLeapYear2(2010)

SQL Server - Check for Leap Year (Part 1)

I have this co-worker who is obsessed about leap year.  This post is dedicated to him.

There are various ways to determine if a given year is a leap year.  The first function here is using the various SQL Date functions to get the job done.

-------------------------------------------------
-- Does the function already exist? 
-- if so... drop it.
-------------------------------------------------

IF OBJECT_ID( N'dbo.xIsLeapYear',N'FN' ) IS NOT NULL
BEGIN
    print 'Function found... Dropping'
    DROP FUNCTION dbo.xIsLeapYear
END

GO

CREATE FUNCTION xIsLeapYear(@Year SmallInt)
RETURNS CHAR(3)
AS

-------------------------------------------------
--Function will Check to See if the year
--passed in is a leap year.
--
--This function uses SQL Server Date functions
--to get the job done. 
--
--You pass in a Year and it returns 'Yes' or 'No'
-------------------------------------------------
BEGIN
    DECLARE @Date   DateTime
    DECLARE @Result Char(3)
   

    -- Turn the Passed in year to Feb 28th
    SET @Date  = Convert( Varchar(4), @Year ) + '0228'
   
    -- Add one Day to the date
    SET @Date  = DateAdd( d, 1, @Date )
   
    -- Is the @date variable still February?
    IF ( DatePart(M, @date) = 2 )
        BEGIN
            SET @Result = 'Yes'
        END
    ELSE
        BEGIN
            SET @Result = 'NO'
        END


    RETURN @Result
END

GO

-------------------------------------------------
--Now we will check to see if the code works
-------------------------------------------------
PRINT ''
PRINT 'Using SQL Server Date functions to check Year.'
PRINT '----------------------------------------------'
PRINT 'Is 1900 a Leap year?  ' + dbo.xIsLeapYear(1900)
PRINT 'Is 2000 a Leap year?  ' + dbo.xIsLeapYear(2000)
PRINT 'Is 2008 a Leap year?  ' + dbo.xIsLeapYear(2008)
PRINT 'Is 2010 a Leap year?  ' + dbo.xIsLeapYear(2010)

SQL Server 2012

I'm very excited to see that the SQL Server 2012 RC0 was recently pushed out.  This has also been known with the code name of "Denali".

Since I mainly work with the Data warehousing and Business Intelligence side of things, I'll post some  of the things that caught my attention...



Microsoft has introduced a new Semantic Model (BISM) to improve Excel, Reporting Services and the  SharePoint experience.It appears that the new Semantic Model is in addition to the current BI Models, so you won't be forced to upgrade your existing stack at launch time.

PowerPivot

1. Supports Hierachies
2. Multiple relationships between tables
3. easily create, edit and manage measures
4. Key Performance Indicators (KPIs) <-- Nice!
5. new DAX functions
1. Time series, distinct counts
    

Analysis Services

1. The above items from the PowerPivot
2. In addition to row-level security
3. partitions 
4. Direct Query Mode
1. enables access of information directly form the source system.

SQL Server - Create indexes on Temp Tables

-------------------------------------------------
--Create temp table to hold values
-------------------------------------------------
CREATE TABLE #myTempTable
(
     ID       int
   , customer varchar(50)
)

-------------------------------------------------
--Create Clustered Index on the ID
-------------------------------------------------

CREATE CLUSTERED INDEX #IX_myTempTable_ID
     ON #myTempTable ( ID )

-------------------------------------------------
--Create Index on customer
-------------------------------------------------

CREATE INDEX #IX_myTempTable_Customer
     ON #myTempTable ( customer ) 

-- Note:  When you drop the table, 

--        the indexes will be dropped at the same time.

SQL Server - Check to see if a temp table already exists

-------------------------------------------------
--Check to see if temp table exists
-------------------------------------------------
IF OBJECT_ID('tempdb..#myTempTable') IS NOT NULL
  BEGIN
   PRINT '#myTempTable Already Exists... Dropping'
   DROP TABLE #myTempTable
  END

ELSE
  BEGIN
    PRINT '#myTempTable does not yet exist...'
  END


-------------------------------------------------
--Create temp table to hold values
-------------------------------------------------
CREATE TABLE #myTempTable
(
     ID       int
   , customer varchar(50)
)

PRINT '#myTempTable Created'

SQL Server - Check to see if a function already exists

So, you need to know if a function already exists in your database?  The code you seek is below.  

Need to know how to create that function as well?  I've created a useless function just for you.

-------------------------------------------------
-- Does the function already exist?
-- if so... drop it.

--

--

-- This will check for the various types of functions

-- Type of:

--   FN = Scalar Function

--   IF = Inline Table Function

--   TF = Table Function

--

-- If you are working with Assembly CLR Functions then you will

-- need to use:

--

--   FS = Assembly (CLR) Scalar Function

--   FT = Assembly (CLR) Table Valued Function

--

-------------------------------------------------IF  EXISTS
    (
        SELECT *
            FROM sys.objects
            WHERE object_id = OBJECT_ID(N'dbo.xTestFunction')
            AND type in (N'FN', N'IF', N'TF')
    )
  BEGIN
   PRINT 'function found... Dropping'
   DROP FUNCTION dbo.xTestFunction
  END

ELSE
  BEGIN
    PRINT 'function does not yet exist...'
  END

GO

-------------------------------------------------
--Create a useless Test Function
-- Pass in a text string
-- Get the same text string back
-------------------------------------------------
CREATE FUNCTION dbo.xTestFunction( @Input NVarChar(MAX) )

RETURNS NVarChar(MAX)
AS

BEGIN
    DECLARE @Result NVarChar(MAX)

    SET @Result = @Input
  
    RETURN @Result
END

GO


-------------------------------------------------
--Now we will check to see if the code works
-------------------------------------------------
Select dbo.xTestFunction('This is my useless text string Input')