T-SQL Search String From Right / Reverse Search

DECLARE @SomeText varchar(255);
DECLARE @TextToSearch varchar(10);

SET @SomeText = 'There are 3 spaces';
SET @TextToSearch = ' ';

Print CharIndex(@TextToSearch, @SomeText);
-- Following line will search @TextToSearch in @SomeText from right
Print
        CASE WHEN CharIndex(@TextToSearch, @SomeText) > 0 THEN
            (Len(@SomeText + '-') - 1) - 
                (CharIndex(Reverse(@TextToSearch), Reverse(@SomeText)) + (Len(@TextToSearch + '-')-1) - 1) + 1
        ELSE
            0
        END

-- Output:
-- -------------------------------------
-- 6
-- 12
-- -------------------------------------
-- Tips: 
--
-- Reverse() function reverses the string
--
-- Len(@SomeText + '-') - 1, returns the actual string length even if
-- @SomeText have space at the end. Note that Len() function ignores
-- the spaces at the end of string that is why we have placed a '-'
-- at the end of string then subtracted 1 from length so that Len()
-- function should return the correct length

Function to Trim any Character/String from Left of varchar in SQL Server

An Extended Left Trim (LTRIM) Function for SQL Server which removes given string/character from Left of another varchar (String)

Level: Intermediate

Knowledge Required:

• T-SQL
• SQL Server 2005
• SQL Server User Define Functions

Description:
The SQL Server's built-in LTrim() function removes only the blanks (spaces) from Left of a varchar (string). Here is a User Defined function which can remove any character.

CREATE FUNCTION [dbo].[LTrimString]
(
 -- Add the parameters for the function here
 @StringToTrim varchar(max),
 @CharToTrim varchar(10)
)
RETURNS varchar(max)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result varchar(max);
    DECLARE @i int;
    DECLARE @l int;
    DECLARE @sl int;

    SET @i = 1;
    SET @l = Len(@StringToTrim);
    SET @sl = Len(@CharToTrim);
    While @i < @l
    Begin  
            If SubString(@StringToTrim, @i, @sl) <> @CharToTrim
            Begin
                SET @Result = SubString(@StringToTrim, @i, @l - @i + 1);
                Break;
            End
        SET @i = @i + @sl;
    End

    SET @Result = IsNull(@Result, @StringToTrim);

    -- Return the result of the function
    RETURN @Result;
END

Usage:

Print dbo.LTrimString('00000005221', '0')

Output:

5221

Try...Catch and Transaction in SQL Server 2005

Level: Intermediate

Knowledge Required:

• T-SQL
• SQL Server 2005
• Stored Procedure

Description:
In Stored Procedure (having lots of INSERTs, UPDATESs and DELETEs) we use Transaction (Begin Tran, Commit Tran, RollBack Tran). We usually begin with Begin Tran and on some error we do RollBack Tran and if NO error occurred we Commit Tran.

A better way to do this task is to use the Try...Catch in SQL Server 2005. For example:

    Begin Try
        BEGIN TRAN;

        INSERT INTO ....

        SELECT ...

        INSERT INTO ...

        DELETE ...
  
        DELETE ...

        INSERT ...

        COMMIT TRAN;
    End Try
    Begin Catch
        ROLLBACK TRAN;

        DECLARE @ErrorMsg varchar(max);
        DECLARE @ErrorSeverity int;
        DECLARE @ErrorState int;

        SET @ErrorMsg = ERROR_Message();
        SET @ErrorSeverity = ERROR_SEVERITY();
        SET @ErrorState = ERROR_STATE();

        RAISERROR(@ErrorMsg, @ErrorSeverity, @ErrorState);
    End Catch

As you can see,

1) We start the Transaction in Try...Catch block
2) Next we move forward and try to do insert, update and delete
3) If any error occurred during these statements the Exception will be caught in the Begin Catch...End Catch block
4) Then in this block we have first Rolled Back the Transaction
5) Then again throw the exception so the caller should know that some error was occurred during processing
6) And if NO exception occured then finally before the End Try we have Committed the Transaction

How to add the Current Login Details while updating a Row (SQL Server 2005)

Introduction: This article explains how to add the Current Login Details (including, Login ID, Date Time and IP Address) while updating a Row in SQL Server 2005.
Level: Intermediate
Knowledge Required:

• T-SQL
• SQL Server 2005
• Table
• Default Column Value
• User Defined Function

Description:
While saving the data in a Table, we can also save the Login Details like Login ID, Date Time and IP Address with each row. This way we can audit the table. To accomplish this we can also handle it in our insert stored procedure but it is better to use the Default Column Values here, since it is much easier and one time job. However we need to do coding in our Update Stored Procedure.

Default Column Value: While inserting the new Row, if we ignore a column and do NOT include in the Column list then SQL Server puts the Default Value (which we have given) to this column also the Column should be Allow Nulls = False, otherwise SQL Server will put NULL into this column.

For example: we have a Table Student as,

• Student_ID
• Student_Name
• Student_FatherName

If we have created a Front-End application and several users are Inserting/Updating the Students. So we cannot track which user inserted that student and which user updated that student. For this purpose we can add 4 columns in our table as,

• Update_User
• Update_DateTime
• Update_IPAddress
• Update_Count

All fields are Allow Nulls = False

1) To Get the Current Login: SUSER_NAME() Function
2) To Get the Current DateTime: GetDate() Function
3) To Get the IP Address: We will create our own Function

So for the above 2 tasks we have built-in functions, but for the 3rd one we need to create our own function. Fortunately we have discussed this issue in my previous post:

How to get Client IP Address in SQL Server 2005

In the same post we have created a User Defined Function, so we are going to use the same function here.

Thats it, now we only need to put the Default Values for our Columns,

Update_User:
Default Value or Binding: Getdate()

Update_DateTime:
Default Value or Binding: SUSER_NAME()

Update_IPAddress:
Default Value or Binding: dbo.GetCurrentIP()

Update_Count:
Default Value or Binding: 0

Note: To set the Default Value for a column (open table in Design mode)
1) Select the Column (of which you want to set Default Value)
2) Locate the "Default Value or Binding" in the Column Properties (5th property)
3) Type the Value which you want to set as Default

Now open the Table and only type ID, Name and Father Name and refresh the Table you will notice that SQL Server has automatically set the Values for other columns.

Next we will update our Update Stored Procedure as,

CREATE PROCEDURE [dbo].[UpdateStudent] 
    @Student_ID int,
    @Student_Name varchar(255),
    @Student_FatherName varchar(255)
AS
BEGIN 
    SET NOCOUNT ON;

    UPDATE tbl_Student
        SET Student_Name = @Student_Name,
            Student_FatherName = @Student_FatherName,
            Update_User = SUSER_NAME(),
            Update_DateTime = GetDate(),
            Update_IPAddress = dbo.GetCurrentIP(),
            Update_Count = Update_Count + 1
    WHERE
        Student_ID = @Student_ID;
End

How to get Client IP Address in SQL Server 2005

Level: Intermediate
Knowledge Required:

• T-SQL
• SQL Server 2005

Description:
While executing some query, sometimes it is required to have the Client's IP Address who is executing this Query.

SELECT client_net_address
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

We can also put this Query into some function which can be used further.

CREATE FUNCTION [dbo].[GetCurrentIP] ()
RETURNS varchar(255)
AS
BEGIN
    DECLARE @IP_Address varchar(255);

    SELECT @IP_Address = client_net_address
    FROM sys.dm_exec_connections
    WHERE Session_id = @@SPID;

    Return @IP_Address;
END

Comparing DateTime Column in SQL Server

Introduction: This article addresses the following issues:

1) Time also stores in DateTime column therefore Time is also required while comparing
2) How to extract only Date (excluding the Time) from DateTime column

Level: Beginner

Knowledge Required:

• T-SQL
• SQL Server 2005

Description:
While working with Database we usually face an issue where we need to compare the given Date with SQL Server DateTime column. For example:

SELECT * FROM Orders WHERE Order_Date = '5-May-2008'

As we can see in above example we are comparing our date i.e. '5-May-2008' with the DateTime column Order_Date. But it is NOT sure that all the Rows of 5-May-2008 will be returned. This is because Time also stores here. So if we look into the Table we will find that,

Order_Date = '5-May-2008 10:30'
Order_Date = '5-May-2008 11:00'
Order_Date = '5-May-2008 14:00'

So when we give '5-May-2008' to SQL Server then it automatically converts it into:

Order_Date = '5-May-2008 12:00'

Therefore this date will NOT be equal to any of the dates above.

There are several techniques to handle this issue, I will discuss some here:

1) Compare all the 3 parts (day, month, year)
2) Convert both (the given Date and the DateTime Column) into some predefined format using CONVERT function
3) Use Date Range

1) Compare all the 3 parts:
Example:

DECLARE @Given_Date DateTime;

SET @Given_Date = '5-May-2008 12:00';

SELECT *
FROM Orders 
WHERE   Day(Order_Date) = Day(@Given_Date) AND
        Month(Order_Date) = Month(@Given_Date) AND
        Year(Order_Date) = Year(@Given_Date);

2) Convert both (the given Date and the DateTime Column) into some predefined format using CONVERT function:
Example:

DECLARE @Given_Date DateTime;

SET @Given_Date = '5-May-2008 12:00';

SELECT *
FROM Orders 
WHERE   CONVERT(varchar(100), Order_Date, 112) = CONVERT(varchar(100), @Given_Date, 112);

Note that CONVERT function will work as:

Print CONVERT(varchar(100), CAST('5-May-2008' AS DateTime), 112);

Output:

20080505

But we have limitation i.e. cannot use '<' and '>' operators here.

3) Use Date Range
Example:

DECLARE @Given_Date DateTime;
DECLARE @Start_Date DateTime;
DECLARE @End_Date DateTime;

SET @Given_Date = '5-May-2008 12:00';
SET @Start_Date = CAST(CAST(Day(@Given_Date) As varchar(100)) + '-' + DateName(mm, @Given_Date) + '-' + CAST(Year(@Given_Date) As varchar(100)) As DateTime);
SET @End_Date = CAST(CAST(Day(@Given_Date) As varchar(100)) + '-' + DateName(mm, @Given_Date) + '-' + CAST(Year(@Given_Date) As varchar(100)) + ' 23:59:59' As DateTime);

SELECT *
FROM Order
WHERE Order_Date Between @Start_Date AND @End_Date;

In my opinion the last example is the fastest, since in all the previous examples SQL Server has to perform some extraction/conversion each time while extracting the Rows. But in the last method SQL Server will convert the Given Date only once, and in SELECT statement each time it is comparing the Dates, which is obviously faster than comparing a Date after converting it into VarChar or comparing each part of Date. Also in previous 2 methods we have limitation i.e. we cannot use the Range.

How to Add SQL Server Built-in/User Defined Function in Typed DataSet as Queries

Issue: SQL Server Function in Typed DataSet does NOT return value
Level: Intermediate
Knowledge Required:

• T-SQL
• SQL User Defined Function
• Typed DataSet
• TableAdapter

Introduction:
This article explains how to add the SQL Server’s User Defined or Built-in Function in Typed DataSet. Note that if we do NOT follow the proper way then Function might NOT return any value in Visual Basic.

Description:
As we have used the Typed DataSets in VB 2005, which are primarily used to store the data, loaded from Database. We can also use the Typed DataSets to execute SQL Server functions (either built-in or user Defined).

For this purpose first I will show you the normal procedure:

1) Add any User Defined Function in the Database
e.g.: The following function returns the server date

CREATE FUNCTION [dbo].[GetServerDate] ()
RETURNS DateTime
AS
BEGIN
    DECLARE @Result AS DateTime;

    SELECT @Result = GetDate();

    RETURN @Result;
END

2) In Visual Studio 2005, create a new Windows Application Project
3) Add a New Empty DataSet (Data->Add New Data Source) (DO NOT select any tables/procedures/etc.)
4) Open the DataSet in Designer
5) Right Click in the Designer and Click on Add->Query


6) In the TableAdapter Query Configuration Wizard, select the "Use existing stored procedure" option (on page 2)


7) In the next step select the GetServerDate procedure from given List


8) Click Finish

This will add a Query TableAdapter in our DataSet and have added a Function GetServerDate which can be called using Code as,

Dim adp As MyDataSetTableAdapters.QueriesTableAdapter
Dim o As Object

adp = New MyDataSetTableAdapters.QueriesTableAdapter
o = adp.GetServerDate()

But here adp.GetServerDate() will always return Nothing. I think this is because internally when TableAdapter executes the Procedure it does NOT pass the parameters properly.

To overcome this issue we will use a slightly different approach.

Proceed the above given steps up to Step 5, then:

6) In the TableAdapter Query Configuration Wizard, this time select "Use SQL Statements" option, click Next
7) Select "SELECT which returns a single value" option, click Next


8) In the next step type the following SQL Query and click Next

SELECT dbo.GetServerDate()

9) Next we will supply the Function Name (which will be used in coding), type GetServerDate here
10) Click Finish

Now we will again test the function with same code given above and you will notice it returns a Value i.e. DateTime on Server.

In the above example we have used the SQL Server User Defined Function, we can also use the SQL Server's Built-in Function. For Example in the above steps, replace the SQL Statement in Step 8 to the following SQL Statement.

SELECT Is_Member(@Role)

Above function will check the Current Login in the Particular Role.

How to Get Multiple Rows from Database using Comma Separated IDs (Primary Keys in Delimited String)

Level: Intermediate

Knowledge Required:

• T-SQL
• SQL Server Stored Procedure
• SQL Server User Defined Table Functions

Description:
Sometimes it is required that we need to send more than 1 primary keys to the Database and get the Rows from Table. For example, we want to get all the Rows in which Primary Key = 1, 23, 66 and 99. For this purpose we can create a Dynamic SQL Query in our Application and then execute it as,

SELECT * FROM tbl_SomeTable WHERE PrimaryKey IN (1, 23, 66, 99)

But if we have done all our work using Stored Procedures then we need to execute the same stored procedure 4 times.

CREATE PROCEDURE dbo.GetSomeTableRow
    @PrimaryKey int
AS
Begin
    SET NOCOUNT ON;

    SELECT *
    FROM tbl_SomeTable
    WHERE PrimaryKey = @PrimaryKey;
End

Now we want to send the IDs to this Stored Procedure in one go. We can achieve this by creating a Delimited String, e.g.: Dim sIDs As String sIDs = "1, 22, 33, 99" Then we will create a stored procedure which will accept this Delimited String and return rows. But before creating this Stored Procedure we will first create a User Defined Table Function which will accept the Delimited String and Return the Table having 1 int Field/Column. This function will extract out each integer value from String and add it in a Table then return that Table.

Following is the Script of this function:

CREATE FUNCTION [dbo].GetIntTableFromDelimitedString
( 
    @DelimitedString varchar(max), 
    @Delimiter varchar(10)
)
RETURNS
@ReturnTable TABLE(
    IntValue int
)
AS
Begin
    DECLARE @EachItem varchar(255);
    DECLARE @DelimiterPos int;
    DECLARE @DelimiterPosPrv int;

    SET @DelimitedString = @DelimitedString + ',';
    SET @DelimiterPosPrv = 1;
    SET @DelimiterPos = CHARINDEX(@Delimiter, @DelimitedString, 1);

    WHILE @DelimiterPos > 0
    BEGIN
        SET @EachItem = LTRIM(RTRIM(SUBSTRING(@DelimitedString, @DelimiterPosPrv, @DelimiterPos - @DelimiterPosPrv)));
        IF @EachItem <> ''
            INSERT INTO @ReturnTable(IntValue)
                VALUES(CAST(@EachItem AS int));  
        SET @DelimiterPosPrv = @DelimiterPos + 1;
        SET @DelimiterPos = CHARINDEX(@Delimiter, @DelimitedString, @DelimiterPosPrv);
    END

    Return;
End

Now we can change our Stored Procedure as

CREATE PROCEDURE dbo.GetSomeTableRows
    @PrimaryKeys varchar(max)
AS
Begin
    SET NOCOUNT ON;

    SELECT *
    FROM tbl_SomeTable
    WHERE PrimaryKey IN (
                        SELECT IntValue 
                        FROM dbo.GetIntTableFromDelimitedString(@PrimaryKeys, ',')
                        );
End