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.

Find and correct patent in string

SPLIT: user defined function

User defined function for Proper Case or Title Case

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