Write Function in SQL Server 2005

Something cool I just ran across today.  It appears that in SQL 2005 they added the 'Write' function.

This allows you to update character column, including that with the size of MAX.  This is useful as STUFF didn't work with varchar(max).

-- Create a Temp table to hold our text
CREATE TABLE #tmpTable
    (
        myText NVARCHAR(MAX)
    )


-- Insert sample text we want to mess with
INSERT INTO #tmpTable
Select 'This a very cool Test!'


-- Use the Write function to put the text 'Hello Test'
-- at the beginning of the field
UPDATE #tmpTable
    SET myText.write(
                        'Hello Test'
                        ,0              -- Start index
                        ,0              -- Number of characters to replace
                    )


-- Show that the text has indeed been pushed to the front of the field
select * FROM #tmpTable


-- Now lets use the Write function to delete the first ten characters
UPDATE #tmpTable
    SET myText.Write(
                        ''              -- An Empty String
                       , 0              -- Start at 0
                       , 10             -- The number of characters to replace
                    )
















                   

SQL Server - Altering tables and renaming columns

 A quick post on how to alter the data types and nullable attributes for a table.

In addition, since you would think that the same command statement would also take care of renaming a column I've added that as well.

First we create a test table to work with, as usual I'll stick with cars.

 After the table has been created, we realized that the Make is only five characters.  That will need to be fixed.

For some reason, it was decided that the Year can not be nullable.  They don't care about the Make or Model, go figure.

Then finally, we are told that Year really shouldn't be used.  They want us to use ModelYear instead.  But wait, ALTER TABLE just doesn't allow column name changes.  So we must use the system stored procedure SP_RENAME.

Below we'll be using SP_RENAME to rename the column.  But it can also be used to rename databases, indexes, objects and user data types.  See: msdn:SP_RENAME for more information.

 
-- Below creates a sample table called cars:

--- Create a Table ---
CREATE TABLE dbo.Cars
(
    ID     INT          Identity(1,1) NOT NULL
  , Year   INT         NULL
  , Make   VarChar(05) NULL
  , Model  VarChar(50) NULL
)

GO

-- the Alter TABLE statement allows you to modify the
-- data type and the nullable attribute of a column.


-- Here we are increasing the size of the Make
-- column to 50 characters from the initially created 05
ALTER TABLE dbo.Cars
    ALTER COLUMN Make Varchar(50) NULL
   
 
GO    

-- Let's prevent null values from being entered
-- into the Year column. 
--Note: If there are already null values in the
--Year column, this command would fail.
ALTER TABLE dbo.Cars
    ALTER COLUMN Year INT NOT NULL

GO

-- There is no ALTER command to rename a column
-- for this you will need to use 'sp_rename'
EXEC sp_rename 'dbo.Cars.Year', 'ModelYear', 'column'


--Note: You will receive a caution message indicating that
--      your change could break any existing scripts or
--      stored procedures.  So make sure you update any
--      affected stored procedures or scripts.

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