SQL Server: Trim, replace, substring and indexof #sql #sqlserver

I often have to deal with file paths in SQL, whilst at home in C# I always scratch my head when I need to do string manipulation in SQL, so here’s a quick outline (via an example) of the string manipulation functions I often use and frequently forget.

DECLARE @pathStr AS NVARCHAR(255)
DECLARE @folderPathStr AS NVARCHAR(255)

SELECT @pathStr = 'C:\Temp\another dir\file%20name.ext'
--C:\Temp\another dir\file%20name.ext
--12345678901234567890123456789012345

SELECT @pathStr = REPLACE(@pathStr, '%20', '_')
-- @pathStr is now 'C:\Temp\another dir\file_name.ext'

-- substring 10 characters
SELECT @folderPathStr = LEFT(@pathStr, 10)
-- @folderPathStr is now 'C:\Temp\an'

-- But for folder path we want to take the filename off, so we need the last occurance of the backslash
-- CHARINDEX('\', @pathStr, 1) would return 3, which is the first occurance, if you want the last then reverse the string
-- to get the number of characters from the end, then take this away from the total
-- Note: CHARINDEX is 1 based, but passing 0 here would be fine too.
-- CHARINDEX('\', REVERSE(@pathStr), 1) would return 14
-- LEN(@pathStr) - CHARINDEX('\', REVERSE(@pathStr), 1) would return 19
-- So we can use this to do a substring of length 19, we do this via the LEFT function
SELECT @folderPathStr = LEFT(@pathStr, LEN(@pathStr) - CHARINDEX('\', REVERSE(@pathStr), 1))
-- @folderPathStr is now 'C:\Temp\another dir'

So to summarise, LEN, LEFT (also there is a RIGHT), REPLACE, REVERSE and CHARINDEX are in my normal armory.

Also, we must not forget PATINDEX, this is the pattern matching version of CHARINDEX, which you can use for endswith, startswith and contains:

SELECT * FROM docnames
WHERE PATINDEX('%.doc', val) > 0 -- All .doc files (endswith(.doc))
OR PATINDEX('me%', val) > 0 -- or filenames beginning me (startswith(me))
OR PATINDEX('%error%', val) > 0 -- or filenames contains error (contains(error))
Advertisements

2 responses to “SQL Server: Trim, replace, substring and indexof #sql #sqlserver

  1. Pingback: Oracle: Trim, replace, substring and indexof #oracle | Tim's cycling blog

  2. Awesome! Thx.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s