Using MSSQL to Find a Substring From the Right

Recently I had to add a column to a table that stored urls. The field needed hold the directory that site the files actually live in. So if the record’s url was:

“www.somesite.com/blah/blahblah/thisDirectory/”

I needed to update record’s new field with: “thisDirectory”
The table was large and I needed to update all the records.

I approached this thinking that I’d be able to Update all the records using MSSQL’s RIGHT function to get the characters from the right side of the string, but I’d need to know where the ‘/’ character was.

MSSQL’s CHARINDEX function would allow me to search for the index of the ‘/’ character, but only going forward. I of course needed to search from the end of the string. I know SQL is a limited language, but the ability to search a string backwards seems liked fairly standard functionality.

MSSQL does provide a way: you need to reverse the string and then search it forwards using the REVERSE function.

CHARINDEX('/',REVERSE(urls.server_path))

Below is the MSSQL statement I ended up with:

REPLACE(RIGHT(urls.server_path,CHARINDEX('/',REVERSE(urls.server_path),2)),'/','') AS website_directory

I was glad that there is a way to get the job done, but it feels like a hack.

1 thought on “Using MSSQL to Find a Substring From the Right”

Leave a Reply

Your email address will not be published. Required fields are marked *