You mean the second example I posted didn't work? (The first example was how NOT to do it)...

Recommend you post the actual trigger code and a bit of information on the schema of the tables...

That's because it looks like you're trying to add spaces to a numeric value which has no effect.  Convert the street number to VARCHAR and then add the spaces to...

Sorry, Joe... I misread the post quite badly... thought you were trying to do locked reads.

I realize this is a really old thread, but I read it with some great interest... not one of the folks that claimed that case sensitivity created that mythical 40%...

That requires a change in "COLLATION"... unfortunately, there's a bug in SQL Server that destroys the ability to use collation where a range of letters is included in a LIKE......

Then, the trigger is wrong... it should be able to handle both.

Allen,

First, there is no reason, whatsoever, to store the "M" in the sequence table.  All it does is slow everything down with unneccessary character conversions.

Using the good example that Mr....

Depending on how and where the function is used in a WHERE clause, it can drastically and negatively affect performance because it may disable the ability for INDEX SEEKs to...