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))