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
Advertisements

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

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

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

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

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

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