SQL Functions – STUFF() and REPLACE()

SQL Functions – STUFF() and REPLACE()

STUFF() can be used to stuff a string into another string. It inserts the string at a given position, and deletes the number of characters specified from the original string.





DECLARE @string1 VARCHAR(20) = 'Microsoft Server'

DECLARE @string2 VARCHAR(20) = 'SQL Server 2005'



SELECT      @string1 + ' -> ' + STUFF(@string1, 11, 0, 'SQL ')

            AS 'String 1',

            @string2 + ' -> ' + STUFF(@string2, 15, 1, '8 R2')

            AS 'String 2'

Result Set:

String 1                                 String 2

—————————————- ————————————-

Microsoft Server -> Microsoft SQL Server SQL Server 2005 -> SQL Server 2008 R2



(1 row(s) affected)

In the first string it inserts 'SQL ' at specified position – 11, the third argument 0 indicated the number of characters to be deleted before inserting the new string.

For second string, we have deleted one (1) character starting from position 15, which deletes '5', and then it inserts the new string at position 15 – '8 R2'.



REPLACE():

REPLACE() replaces all the specified characters with new characters.

DECLARE @string3 VARCHAR(35) = 'sql 2005, sql 2008, sql 2008 r2'



SELECT @string3, REPLACE(@string3,'sql','SQL')

Result Set:

———————————–      ———————————–

sql 2005, sql 2008, sql 2008 r2   SQL 2005, SQL 2008, SQL 2008 r2



(1 row(s) affected)

However, it is not limited to same number of characters:

DECLARE @string3 VARCHAR(35) = '2008 R2'



SELECT @string3, REPLACE(@string3,'20','SQL Server 2')

Result Set:

————–       ————————

2008 R2              SQL Server 208 R2



(1 row(s) affected)

Hope This Helps!

SYSDATETIME versus GETDATE

In SQL Server 2005 (and previous versions) one would use GETDATE() to get the current date and time. In SQL Server 2008 this is still possible, but one can also use SYSDATETIME(). So what is exactly the difference?

The function GETDATE() returns the seconds part of the time in three fractions. The function SYSDATETIME() however returns the seconds part of the time in seven fractions. This is demonstrated in the following example:

select 'The current date and time is: ' , GETDATE()

select 'The current date and time is: ' , SYSDATETIME()

The current date and time is:  2008-08-21 16:28:50.340
The current date and time is:  2008-08-21 16:28:50.3406250

Thanks 

Alok Kumar Sharma 

DATETIME2 vs DATETIME in SQL Server 2008

DATETIME2 vs DATETIME in SQL Server 2008
The DATETIME2 is a new data type in SQL Server 2008.
We all know of the existence of the DATETIME.

So, what is the difference between these two data types?

Let's try to compare them with some examples.

--Comparison 1: Notice the date - Both data types work fine
select cast('1753-01-01 18:00:00.123' as DATETIME) as [datetime]
Result (Success): 1753-01-01 18:00:00.123

select cast('1753-01-01 18:00:00.123' as DATETIME2) as [datetime2]
Result (Success): 1753-01-01 18:00:00.1230000

Comments: Please note the precision of the DATETIME2. It provides support up to nanoseconds!


--Comparison 2: Notice the time precision
select cast('1753-01-01 18:00:00.1234' as DATETIME) as [datetime]
Result (Error): Conversion failed when converting date and/or time from character string

select cast('1753-01-01 18:00:00.1234' as DATETIME2) as [datetime2]
Result (Success): 1753-01-01 18:00:00.1234000

Comments: DATΕTIME does not support time precision more than milliseconds and that's why the above conversion fails. Though, the DATETIME2 supports up to nanoseconds and the conversion works.


--Comparison 3: Notice the date values
select cast('1653-01-01 18:00:00.123' as DATETIME) as [datetime]
Result (Error): The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

select cast('1653-01-01 18:00:00.123' as DATETIME2) as [datetime]
Result (Success): 1653-01-01 18:00:00.1230000

Comments: DATΕTIME does not support date values before the year 1753 and that's why the conversion fails. Though, DATETIME2 supports until back to year 0001 and so the conversion works.

Conclusions: The DATETIME2 offers support for larger date ranges and larger time precision.


DATETIME: Date and Time Ranges
------------------------------------
The supported date range is:1753-01-01 through 9999-12-31 (January 1, 1753, AD through December 31, 9999 AD)

The supported time range is: 00:00:00 through 23:59:59.997


DATETIME2: Date and Time Ranges
-------------------------------------
The supported date range is: 0001-01-01 through 9999-12-31 (January 1,1 AD through December 31, 9999 AD)

The Supported time range is: 00:00:00 through 23:59:59.9999999


Thanks
Alok Kumar Sharma

Getting Age in Text or Date Difference in text format

/*----------------------------------------------------------------------------------------------------------------------------
  Author     :-    Alok Kumar Sharma
  Purpose    :-    To find the datediff/age in text format (eg. 1 year(s), 10 month(s), 10 day(s)).              
  DATE        :-   07-July-2012
  DATABASE    :-     SQL

----------------------------------------------------------------------------------------------------------------------------*/
IF (Select COUNT(*) From Sysobjects Where [name] like 'FN_GETDATEDIFFTEXT') > 0
BEGIN
    DROP FUNCTION FN_GETDATEDIFFTEXT
END
GO
CREATE FUNCTION FN_GETDATEDIFFTEXT(@FromDate DateTime, @ToDate DateTime)
RETURNS NVARCHAR(50)
AS
BEGIN  

    Declare @daysDiff Int
    Declare @monthDiff Int
    Declare @yearDiff Int

    --Select @daysDiff = DATEDIFF(DAY, @FromDate, @ToDate)
    Set @monthDiff = ABS(DATEDIFF(MONTH, @FromDate, @ToDate)%12)
    Set @yearDiff = ABS(DATEDIFF(YYYY, @FromDate, @ToDate))

    -- If the From date month is greater than the month of the To date and the year difference is greater than zero
    -- then the year should the deducted by one
    IF DATEPART(MONTH,@FromDate) > DATEPART(MONTH,@ToDate) AND @yearDiff > 0
    BEGIN
        Set @yearDiff = @yearDiff - 1
    END

    IF DATEPART(DAY,@FromDate) > DATEPART(DAY, @ToDate)
    Begin
        --Get last date of the month of the FromDate
        Declare @lastDateOfMonth DateTime = DATEADD(MONTH, 1, @FromDate)  
        Set @lastDateOfMonth = '01-' + DATENAME(MONTH,@lastDateOfMonth) + '-'+DATENAME(YEAR,@lastDateOfMonth)
        Set @lastDateOfMonth = DATEADD(DAY, -1, @lastDateOfMonth)
      
        Set @daysDiff = DATEDIFF(DAY, @FromDate, @lastDateOfMonth)
        Set @daysDiff = @daysDiff + DATEPART(DAY, @ToDate)
        Set @monthDiff = @monthDiff - 1
    End
    ELSE
    BEGIN
        Set @daysDiff = DATEPART(DAY, @ToDate) - DATEPART(DAY, @FromDate)
    END

    -- Select @yearDiff Yr, @monthDiff Mn, @daysDiff Dy
    RETURN
        CAST(@yearDiff as nvarchar) + ' year(s), ' +
        CAST(@monthDiff as  nvarchar) + ' month(s), ' +
        CAST(@daysDiff as nvarchar) + ' day(s)'
END
GO

-- Select DBO.FN_GETDATEDIFFTEXT('30-Dec-2010', '31-Jan-2011')
-- Select DBO.FN_GETDATEDIFFTEXT('01-Jan-1990', Getdate())

Check IsAlphaNumeric In SQL Server

CREATE FUNCTION dbo.IsAlphaNumeric
(@input varchar(100))
RETURNS bit
AS
BEGIN

declare @i int, @max int, @c varchar(1), @asc int

declare @isAN bit

set @max = LEN(@input)

set @isAN= 1
set @i = 0
while @i < @max begin
    set @i = @i + 1
    set @c = SUBSTRING(@input,@i,1)
    set @asc = ascii(@c)
    set @isAN =
        case when @asc between 48 and 57 then 1 -- 0 9
        when @asc between 65 and 90 then 1 -- A Z
        when @asc between 97 and 122 then 1 -- a z
        when @asc = 32 then 1 --space
        else 0
        end
        
    if @isAN = 0 begin
        return @isAN --not alpha
    end


end
return @isAN -- is alpha

END

Selecting item from ASP.NET listbox using jquery

 $("#btnSave").click(function() {

 var varClassID="";
                $('#ddlChildClass :selected').each(function(i, selected){
               if(varClassID=="")
                    varClassID=$(selected).val();
               else
                    varClassID=varClassID+","+$(selected).val();
                 });

}

====================================


<asp:ListBox ID="ddlChildClass" runat="server" SelectionMode="Multiple">
                    </asp:ListBox>


<input id="btnSave" type="button" value="Save" class="buttonId" runat="server" />