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.

Advertisements

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:

WordPress.com Logo

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

Google photo

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