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.

Advertisement

SQL Server 2008 "Saving Changes is not permitted" error

Work just upgraded us developers to SQL Server Management Studio 2008. Developing in the test database I’m often quickly creating tables or modifying tables through Enterprise Manager. Once I was upgraded to 2008 I kept getting this error:

Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

I looked through the options but I could not find it. I can never find the option I’m looking for in MS products. A quick Google search showed me that it was right there all along. For anyone else that is blind and can’t navigate the menus you can turn this off here:

Tools->Options->Designers->Table and Database Designers->Prevent Saving changes that require table re-creation.