test if first character of a value is upper case

Question

test if first character of a value is upper case

isaac_654135

Ten Centuries

Points: 1255
More actions
September 19, 2017 at 1:25 pm

#402501

I want to test whether or not the first character of every value is upper case, and the rest are lower case. I.E., if they are currently stored as proper case.
Is there a simple way to do this? Or do I have to test whether the value collate C.I. is in(a bunch of possible values) ?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

you should only have to cast the comparison with a collate statement;
this worked for me, does this help?

i used 128 for the length, since a object name is no longer than that.
when you say Propercase, do you mean the second word in a string should be upper cased also?
so the table name [Invoice Details]? i have an inline propercase function for something like that somewhere.
select CASE WHEN UPPER(LEFT(name,1)) + LOWER(SUBSTRING(name,2,128)) = name collate Latin1_General_BIN THEN 'Expected Format' ELSE 'Bad Format' END, * FROM sys.tables

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

isaac_654135 Ten Centuries Points: 1255 More actions · Answer 2

This is a value in a varchar column, though, not sys tables with object names ... But I see what you mean. (or I guess you were just putting that as an example - ok, makes sense, thanks).
I need to brush up on collate statements as tools for comparing things with case sensitivity or not.

In my case, there is only going to be one word or phrase, I just want to verify that the first character of the first word (if there are multiple words) is capital, and the rest are not capital.
So are all collate statements case sensitive unless otherwise specific? meaning I could use any one of a bunch of different collate statements, as long as none of them specific CI ... do I understand that correctly?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

pisorsisaac - Tuesday, September 19, 2017 2:09 PM
This is a value in a varchar column, though, not sys tables with object names ... But I see what you mean. (or I guess you were just putting that as an example - ok, makes sense, thanks).
I need to brush up on collate statements as tools for comparing things with case sensitivity or not.
In my case, there is only going to be one word or phrase, I just want to verify that the first character of the first word (if there are multiple words) is capital, and the rest are not capital.
So are all collate statements case sensitive unless otherwise specific? meaning I could use any one of a bunch of different collate statements, as long as none of them specific CI ... do I understand that correctly?

He was showing you an example. Use your own table and data.

sgmunson SSC Guru Points: 110639 More actions · Answer 4

pisorsisaac - Tuesday, September 19, 2017 2:09 PM
This is a value in a varchar column, though, not sys tables with object names ... But I see what you mean. (or I guess you were just putting that as an example - ok, makes sense, thanks).
I need to brush up on collate statements as tools for comparing things with case sensitivity or not.
In my case, there is only going to be one word or phrase, I just want to verify that the first character of the first word (if there are multiple words) is capital, and the rest are not capital.
So are all collate statements case sensitive unless otherwise specific? meaning I could use any one of a bunch of different collate statements, as long as none of them specific CI ... do I understand that correctly?

COLLATE applies to what it follows. If you want to see the effects, try a SELECT of something both with and without a given collation. Another thing you can do is to look at the ASC() function for any given character to see what the actual ASCII character code is. Better yet, try comparing a value to itself with two different collations, where one is case sensitive, and the other is not. A mixed-case value is the ideal comparison, but it gets more valuable when you also do the same thing for a comparison of 'aaaaa' with 'AAAAA' under the same collation pair conditions. You'll get it figured out fairly quickly.

The reason Lowell suggested using Latin1_General_BIN is because that collation causes a binary character by character comparison, and I think you can understand why.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)