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

Friday, January 29, 2010 |

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

0 comments: