Is field upper or lower case - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Is field upper or lower case

Author

Topic

vusil
Starting Member

22 Posts

Posted - 2004-07-16 : 06:47:17

Hello gurus,
Some help about checking the case of a field.

We have a system that inserts stock location data into a table. No front-end input validation is done on the case of the location entered. When retrieving location info - same app. - for some strange reason it sees different case locations as 2 totally different locations, eg sparta and SPARTA as different locations.
The totals for the each location are then wrongly grouped and other problems. We've written a trigger to capitalize everything on insert.
Now we have to do a once-off update of the location column.
How do I update only the non upper case locations?

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-16 : 06:52:48

how about:

update table
set colnm= upper(colnm)
where colnm <> upper(colnm)

BTW - if you wanted data in upper, you could use possibly use upper on your insert, rather than having a trigger do it?

CiaO

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-16 : 06:55:43

Hmm - on testing I found that not to work - sorry

- probably because I have case-insensitive sort code page.

What is your code page for the server?

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

mr_mist
Grunnio

1870 Posts

Posted - 2004-07-16 : 06:58:31

Initial thought - if this is a one-off, why not just update everything?

Otherwise, I'm assuming the DB is case-sensitive. You could use

UPDATE blah set blah = UPPER (blah) where upper(blah) != blah

methinks. But I'm guessing that would be just as slow as just updating everything, if not slower.

-------
Moo. :)

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-16 : 07:02:06

Moo - stop copying my posts

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

mr_mist
Grunnio

1870 Posts

Posted - 2004-07-16 : 07:07:27

As if I would ever do that :p

It works on case sensitive DBs.

-------
Moo. :)

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-16 : 07:57:44

Thought so ..

That solve the problem, vusil?

You might want to look at the code pages you have, and determine whether your server's are on different code pages.

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Kristen
Test

22859 Posts

Posted - 2004-07-16 : 08:46:29

So much wasted typing effort, Wanderer:

update table
set colnm = colnm
where colnm <> upper(colnm)

the trigger can take care of the rest

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-16 : 08:47:43

quote:

...
where colnm <> upper(colnm)

for a case insensitive box, that condition will never be true. A solution, which I've seen posted here before somewhere, is to convert to varbinary() and then compare.

where convert(varbinary(100), column) <> convert(varbinary(100), column)

the binary conversion will convert 'A' differently from 'a'.

- Jeff

Kristen
Test

22859 Posts

Posted - 2004-07-16 : 09:09:17

(vusil is on a case sensitive box, but.)

By the by, and rather off at a tangent, a solution I sometimes use is to join the offending field to an FK table and set it to the value in that. This only works for FK-able columns of course ... :)

So the following will appropriately capitalise a column:


UPDATE U
SET    MyTableNameColumn = O.name
FROM   MyTablesListTable U
       JOIN sysobjects O
           ON O.name = U.name
          AND O.type = 'U'

Can be useful for Country Names [mixed case assumed] and the like, but will of course work for Product Codes [all-caps alphanumeric assumed] and things of that nature that exist in what might be termed a Dictionary Table.

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-16 : 09:21:09

quote:

vusil is on a case sensitive box

oops didn't see that ... thanks!

- Jeff

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-07-16 : 10:43:05

If the columns are case-insensitive, just use COLLATE in the comparison to make the test case-sensitive. e.g.

WHERE (colname COLLATE SQL_Latin1_General_CP1_CS_AS) <> UPPER(colname)

Kristen
Test

22859 Posts

Posted - 2004-07-17 : 03:48:08

Now that I LIKE!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources