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:


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.


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.

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

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.