CHARINDEX and PATINDEX with STUFF function for string manipulation in SQL Server 2008

Whenever it comes to the string manipulation, people stops thinking about SQL Server and start thinking about how we can do it from front-end programming. This is not always true. I agree that SQL Server is not for string manipulation but in many scenarios, it becomes easy to manipulate string in SQL Server itself rather than in front-end programming.
Let us see one example about this.
Suppose we are storing pointer to image in our database and suddenly folder structure get changed and we have to update path in SQL Server.  In this situation two very popular string manipulation functions CHARINDEX and PATINDEX comes to our help. There are several ways in SQL Server with which we can manipulate the string. I am going to show you two different ways today.
–create table and insert some dummy data
create table StringManipulate
(
      ImagePath varchar(100)
)
GO
INSERT INTO StringManipulate
SELECT ‘~/ROOT/IMAGES/11/ONE.jpg’ UNION ALL
SELECT ‘~/ROOT/IMAGES/1/TWO.jpg’ UNION ALL
SELECT ‘~/ROOT/IMAGES/111/THREE.jpg’ UNION ALL
SELECT ‘~/ROOT/IMAGES/111/FOUR.jpg’ UNION ALL
SELECT ‘~/ROOT/IMAGES/111/FIVE.jpg’
GO
Now, let us think that we are going to remove subfolders of IMAGES and going to put all the images directly into the IMAGES folders so we have to update path accordingly in SQL Server also. We have sub folder from 1 to 999 in our IMAGES folder.
Before we make actual update, let us first SELECT it and will see how it will look.
–First and efficient way with PATINDEX to find a pattern
–and STUFF to manipulate string
SELECT    
      CASE
      WHEN PATINDEX(‘%/[0-9]/%’, ImagePath) > 0 THEN STUFF(ImagePath, PATINDEX(‘%[0-9]/%’, ImagePath), 2, )
      WHEN PATINDEX(‘%/[0-9][0-9]/%’, ImagePath) > 0 THEN STUFF(ImagePath, PATINDEX(‘%[0-9][0-9]/%’, ImagePath), 3, )
      WHEN PATINDEX(‘%/[0-9][0-9][0-9]/%’, ImagePath) > 0 THEN STUFF(ImagePath, PATINDEX(‘%[0-9][0-9][0-9]/%’, ImagePath), 4, )
    END
FROM StringManipulate
WHERE      PATINDEX(‘%/[0-9]/%’, ImagePath) > 0
            OR PATINDEX(‘%/[0-9][0-9]/%’, ImagePath) > 0
            OR PATINDEX(‘%/[0-9][0-9][0-9]/%’, ImagePath) > 0
GO
–second way with CHARINDEX and very popular string manipulation functions
–LEFT and RIGHT
select left(ImagePath,14)+right(ImagePath,len(ImagePath)-charindex(‘/’,ImagePath,15))
from StringManipulate
GO
Once we are confident about our manipulation in SELECT statement, we will move to update our records in table. We will use same above SELECT query’s logic in UPDATE statement to actually manipulate string.
–update with first method
update StringManipulate set ImagePath=CASE
      WHEN PATINDEX(‘%/[0-9]/%’, ImagePath) > 0 THEN STUFF(ImagePath, PATINDEX(‘%[0-9]/%’, ImagePath), 2, )
      WHEN PATINDEX(‘%/[0-9][0-9]/%’, ImagePath) > 0 THEN STUFF(ImagePath, PATINDEX(‘%[0-9][0-9]/%’, ImagePath), 3, )
      WHEN PATINDEX(‘%/[0-9][0-9][0-9]/%’, ImagePath) > 0 THEN STUFF(ImagePath, PATINDEX(‘%[0-9][0-9][0-9]/%’, ImagePath), 4, )
    END
WHERE      PATINDEX(‘%/[0-9]/%’, ImagePath) > 0
            OR PATINDEX(‘%/[0-9][0-9]/%’, ImagePath) > 0
            OR PATINDEX(‘%/[0-9][0-9][0-9]/%’, ImagePath) > 0
–let us check whether our UPDATE has properly been made or not.
SELECT * FROM StringManipulate
GO   
–update with second method.
–if you have already run first update given above, you have to do this UPDATE in another table with same data
–if you will run this UPDATE statement with modfied data, it won’t give you expected results.
UPDATE StringManipulate SET ImagePath=left(ImagePath,14)+right(ImagePath,len(ImagePath)-charindex(‘/’,ImagePath,15))           
SELECT * FROM StringManipulate
GO
Isn’t it easy to do in SQL Server rather than C#?
BTW, I had written some more articles about string handling and CHARINDEX. If you wish, have a look.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Get depended object from SP_Depends and Information_Schema.routines in SQL Server 2008

SP_Depends is very useful stored procedure which can give you list of the dependent object of your table or views. If you pass table name in SP_Depends, it will give you depended views, stored procedures, functions etc. let us look at this by small demo.

–create one small database for testing
create database DependCheck
go
use DependCheck;
go
–create one table and insert some data
create table TestDepend
(
ID Int Identity(1,1),
Name Varchar(20)
)
GO
Insert into TestDepend
SELECT ‘RITESH’ UNION ALL
SELECT ‘RAJAN’ UNION ALL
SELECT ‘ALKA’
go
–create one PROC which is depend on TestDepend Table
CREATE PROC uspTestDependSelectAll
AS
SELECT * FROM TestDepend
GO
–try executing PROC
EXEC uspTestDependSelectAll
GO
–check SP_Depends system procedure to get all dependent objects
sp_depends ‘TestDepend’
GO
–here is one alternate way to get dependent list
SELECT * FROM information_schema.routines ISR WHERE charindex(‘TestDepend’, ISR.ROUTINE_DEFINITION)>0
GO
–USE master;
–GO
–DROP DATABASE DEPENDCHECK
–GO
Well we have seen two different ways to get list of dependent objects of tables but in my live database when I run both way, it gives me different results however in our case it will give same results. So the question I would like to ask is, which way is correct and reliable?

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

ORDER BY clause and function with alias column name in SQL Server

ORDER BY clause is really tricky sometime.  It doesn’t accept alias column name in some situation nor it is being used in Subquery. However we do have some alternate way with the trick to do so.

There was small discussion going about this on Vijaya’s Blog Where I have participated.  .NET MVP Vijaya’s raised the question about this topic and I found it interesting that is why I am sharing it here.

Basically he asked readers that why Order By is working with column alias

For eg:

SELECT
LoginID,
HireDate,
GETDATE() AS Current_Dt
FROM HumanResources.Employee
ORDER BY Current_Dt
And why the same alias doesn’t work with the function in ORDER BY clause.

For eg:

SELECT
LoginID,
HireDate,
GETDATE() AS Current_Dt
FROM HumanResources.Employee
ORDER BY ABS(DATEDIFF(DAY,Current_Dt,HireDate));

I found one trick with CTE which can work for this:

For Eg:

with cte as
(
SELECT
LoginID,
HireDate,
GETDATE() AS Current_Dt
FROM HumanResources.Employee
)select loginid,hiredate,current_dt from cte
ORDER BY ABS(DATEDIFF(DAY,Current_Dt,HireDate))

I would like my blog reader to participate there. Here is the link for that article. DotNetVJ

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Set update trigger to keep watch on certain column’s update in SQL Server 2008

I have seen many times that people keep asking the question how to track the updates in certain column.Well there are several ways to do so but the oldest way is to set UPDATE trigger on the table and keep an eye on the important column.

Please be informed that excesses use of trigger may affect performance so use this method carefully not for keeping track of so many columns in so many tables.

Anyway, let us get back to our core topic and see one small script.

–create one table for demonstration
CREATE TABLE TriggerTest
(
UserName varchar(20),
[Password] varchar(20)
)
GO
–create one table which will LOG information
CREATE TABLE TriggerTestLog
(
ID INT identity(1,1),
UserName varchar(20),
OldPassword varchar(20),
NewPassword varchar(20),
DateOfUpdate datetime
)
GO
–insert some records in first table
INSERT INTO TriggerTest
SELECT ‘ritesh’,‘shah’ union all
SELECT ‘rajan’,‘shah’ union all
SELECT ‘bihag’,‘goodluck’ union all
SELECT ‘bhaumik’,‘shruti’
GO
–set after update trigger for first table
–when password will be updated, it will log
–information in second table.
Create TRIGGER TrgTriggerTest ON TriggerTest
   AFTER Update
AS
BEGIN
      SET NOCOUNT ON;
      IF UPDATE([Password])
      BEGIN
            insert into TriggerTestLog(username,OldPassword,NewPassword,dateOfUpdate)
            select i.username,d.[password],i.[password],getdate()
            from inserted i join deleted d on i.username=d.username
      END
END
GO
–select records from second table
–which is blank right now ofcourse
select * from TriggerTestLog
GO
–update first table
UPDATE TriggerTest SET [Password]=‘furious’ WHERE UserName=‘ritesh’
GO
–you will get information automatically inserted in second table.
select * from TriggerTestLog
GO
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

SET ROWCOUNT in SQL Server 2008 with T-SQL and SSMS

Today morning one of my junior .NET developer was trying to execute query and he was getting only few number of rows from result set since we have millions of records in that tables. He was frustrated and came to me for this reason. Actually this is not a big deal but due to lack of understanding and knowledge it becomes hard to find out why this was happening.

When I checked his SSMS->Tools->Options->Query Execution->General->Set RowCount, I found that there was a value 50 set so it was returning only 50 rows. Actually It suppose to be 0 to return all rows. This option is being set when we want only few rows out of the query and wanted to stop query execution once we achieve those rows.

This command is somehow similar to TOP clause but there is a difference which I will cover in later article.
Anyway, we saw above that how we can set ROWCOUNT from GUI now I will show you one simple script which will do the same.

Create Table emps
(
      name varchar(20),
      dept varchar(20)
)
Insert into emps (Name,Dept)
Select ‘Ritesh’,‘MIS’ union all
Select ‘Rajan’,‘Acct’ union all
Select ‘Bihag’,‘DBA’
GO
SET ROWCOUNT 2;
SELECT * FROM emps
–don’t forget to set 0 again
–otherwise all query will return 2 rows
SET ROWCOUNT 0;
GO
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Introduction of System Databases in SQL Server 2008

Today I saw one question in Experts-Exchange.com which inspires me to write this introduction of system databases. Question asker has used Master database to create his tables for his application, as time passed, application became big and was using so many tables which he has generated in Master database.  Somebody has updated him not to use Master database for user tables than he came to know what mistake he did and raised the question in forum that how can he decided what table belongs to system and what table belongs to his application and how can he move those tables.

This kind of situation may occur if you are not SQL Server expert and you don’t know what the system database is and how it is useful.

Anyway, let me come back to the point of system database in SQL Server 2008. Basically there are four most important system databases which you shouldn’t touch for creating your own user tables, views etc. and also afraid to change any table and/or information in such databases.

Here is the list of four most important system databases which ships with Microsoft SQL Server 2008 by default.

1.)    Master
2.)    Model
3.)    MSDB
4.)    TempDB

Let us look a brief description of each database.

Master Database:  Master is one of the very crucial databases. No Master database No SQL Server J it is composed of system tables that keep track of your whole SQL Server and its installation along with all databases which will be going to created in the server.  It also keeps records of your disk space, system wide configuration, file allocation, logins and existence of other databases to name a few.  So as long as possible, don’t make any change in MASTER database and keep a latest backup copy always with you.

Model Database:  Model database has its own importance. You can say it a template of all other databases. It has a standard set of objects within it so whenever you create any new database in your server, all objects from Model database will be inherited so if you want your new database to get generated with some default objects or permission, put it in Model Database and you are done.

MSDB Database:  MSDB database is being used by SQL Server agent which used to handle all schedule jobs for you.

TempDB Database: TempDB database has really interesting behavior. It used to regenerate every time you restart your SQL Server service. It is basically a workspace and temporary table (global and local both) created by user, used to get stored in tempDB by default.  Some intermediate operation like query processing and sorting also use TempDB database so it would be beneficial to keep TempDB on separate and fast drive in order to get performance.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Upgrade from SQL Server 2000 to SQL Server 2005

There were major changes in architecture & engine of SQL Server 2005 as compare with SQL Server 2000. So as per my observation and experience in live environment, many different kind of error used to come while upgrading, I don’t mean that upgrade doesn’t work at all but personally I have came across so many different kind of error so rather than upgrading from SQL Server 2000 to SQL Server 2005, I would like to follow different approach which has been given below.

1.)    Detach all use databases (don’t need to have system databases with us)
2.)    Back up all the job if you have any in your SQL Server 2000
3.)    Create login transfer script so that all logins of your SQL Server 2000 works fine in SQL Server 2005 with same passwords. There are few different approaches for this but I would like to point out one of the Microsoft’s link for this task. http://support.microsoft.com/kb/246133
4.)    Now, this is the time to uninstall SQL Server 2000
5.)    Install SQL Server 2005 and upgrade it with latest hot fixes and patches
6.)    Attach all your databases which were detached from SQL Server 2000
7.)    Restore all jobs which were backed up from SQL Server 2000
8.)    Run the transfer login script which we have generated in step 3 above.

Isn’t it simple to go for? I find this easiest way to go for rather than upgrading the server. This is my personal point of view which I used to follow. The main reason of writing this article is, yesterday one of my friend was upgrading his SQL Server 2000 to 2005 and came across one weird error which he was not able to solve. I suggested him to follow these steps in future and also suggested few steps which might resolve the error he was facing. I used to get update from him today, If the way I suggested had helped him to resolve the error, I will provide full case study in my blog so everybody can take advantage of the same.

Reference: Ritesh Shah               

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

7th Position as an Expert of the year in Experts-Exchange.com

I am actively running my blog SQLHub.com from March-2009 and have started participated actively in Experts-Exchnage.com from the April-2009.  From the month of May 2009 I was in the list of top 5 experts in few zones like SQL Server, SQL Server 2005 and SQL Server 2008 etc. Finally today experts of the year list came out from Experts-Exchange official and I am really very glad to see my name in the TOP-10 Expert of year.
Since last two months I am really very busy with heavy work load but this name in TOP-10 list encouraged me enough to participate more with community and reach to more and more people. I have learned lot of things from community so I consider it my duty to helping more and more people.
Thanks to everybody who always encouraged me in my mission especially Mr. Pinal Dave who never let myself stop helping people. He is real source of encouragement and inspiration.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah