I need to identify rows with the presence of any upper case character in my varchar(20) column after first character that is in substring(<column name>, 2, len(<column name>)-1).

I've tried COLLATE SQL_Latin1_General_CP1_CS_AS but without much of the success.

Any clue?

Thanks,
Prabodh

__________________
Prabodh

Try using Latin1_General_BIN... guaranteed to be case sensitive...

--Jeff Moden

Thanks it works. Along with that I've explored another way of casting into varbinary.


Prabodh


Prabodh

Two way street here, Prabodh... tell us more details about your other way of casting into varvinary.

--Jeff Moden

Sure Jeff. Casting into varbinary actually makes a comparision case sensitive.
Following code will pick all the rows from Authors table where city column have occurrences of CAPS letters after first char. The idea is to put up a self join with one table having city field (or any desired one) converted into lower case and match it with original value of other table.

select auth1.* from authors auth1
join authors auth2
on auth1.au_id = auth2.au_id
where cast((substring(auth1.city, 2, len(auth1.city)-1)) as varbinary(20))
    <> cast((lower(substring(auth2.city, 2, len(auth2.city)-1))) as varbinary(20))



Prabodh

Thanks, Prabodh,

I think you'll find there's a pretty stiff performance penalty for doing conversions like that instead of just using a collation option.

Now you've got me cursious... why aren't you checking the first letter of the cities?

--Jeff Moden

It was just an example and the actual requierment is something else. Since the target table isn't going to have high number of rows and usage are also minimal I guess I can afford it for now. I'll take care of your performance hint anyway.



Prabodh