ALL CAPS PROBLEM - 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

General SQL Server Forums

New to SQL Server Programming

ALL CAPS PROBLEM

Author

Topic

ConradK
Posting Yak Master

140 Posts

Posted - 2009-12-04 : 15:18:38

In a perfect world I would write code like this.

SELECT

p.ProductCode AS "productcode"
, pe.UPC_code AS UPC

FROM Products p
INNER JOIN Products_Descriptions pd ON p.ProductID = pd.ProductID
INNER JOIN Products_Extended pe ON pd.ProductID = pe.ProductID

WHERE (p.HideProduct is NULL OR p.HideProduct <> 'Y')
AND (pe.metatag_description IS $%~!!!In ALL CAPS!!!~%$)
ORDER BY p.ProductCode

but we are not in a perfect world.

How can I get it to give me a list wherein all the data in a certain column is in ALL CAPS?

To be clear, some of the data is already in ALL CAPS, other is not. I want to be able to look only at the data in ALL CAPS.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-04 : 15:51:40

You can use the UPPER function.

SELECT UPPER(Column1)
FROM SomeTable

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

ConradK
Posting Yak Master

140 Posts

Posted - 2009-12-04 : 15:58:11

Wont that MAKE them uppercase, instead of finding the ones that are uppercase?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-04 : 16:02:37

I'm just showing you an example of how to use the UPPER function.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-04 : 16:02:59

Are you using a case sensitive collation?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-12-04 : 16:15:11

Not going to be terribly efficient if there are a lot of records but this will do it

create table #t (a varchar(10))

insert #t values ('Russell');
insert #t values ('russell');
insert #t values ('RUSSELL');

SELECT * FROM #t WHERE Convert(varbinary, a) = Convert(varbinary, UPPER(a))

ConradK
Posting Yak Master

140 Posts

Posted - 2009-12-04 : 16:25:28

there are three thousand records

some are like

AjkjdkjAjkdjkdfDjdklajkldjd

others like

djkfajdflkaj

and more like

AJAKLJDFKLJADKLFJALDJFLAAKLDJF

I need to find the ones that are like AJAKLJDFKLJADKLFJALDJFLAAKLDJF and have it spit out only those

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-04 : 16:49:18

Russell's solution should work for you...no?

raky
Aged Yak Warrior

767 Posts

Posted - 2009-12-07 : 01:34:14

Hi u can also try this

create table #t (a varchar(10))

insert #t values ('Russell');
insert #t values ('russell');
insert #t values ('RUSSELL');

select * from #t where a = UPPER(a) collate latin1_general_cs_as

raky
Aged Yak Warrior

767 Posts

Posted - 2009-12-07 : 04:53:47

quote:
Originally posted by russell

Not going to be terribly efficient if there are a lot of records but this will do it
create table #t (a varchar(10))

insert #t values ('Russell');
insert #t values ('russell');
insert #t values ('RUSSELL');

SELECT * FROM #t WHERE Convert(varbinary, a) = Convert(varbinary, UPPER(a))

Your solution doesn't work in all conditions. See below example..

create table #t (a varchar(100))

insert #t values ('Russell');
insert #t values ('russell');
insert #t values ('RUSSELL');
insert #t values ('HOLLYWOOD BACKSTORIES: DR. DOLITTLE goes behind the scenes of the hit comedy starring Eddie Murphy.')

Your Solution: SELECT * FROM #t WHERE Convert(varbinary, a) = Convert(varbinary, UPPER(a))
My Solution : select * from #t where a = UPPER(a) collate latin1_general_cs_as
drop table #t

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-07 : 04:59:53

quote:
Originally posted by raky

quote:
Originally posted by russell

Not going to be terribly efficient if there are a lot of records but this will do it
create table #t (a varchar(10))

insert #t values ('Russell');
insert #t values ('russell');
insert #t values ('RUSSELL');

SELECT * FROM #t WHERE Convert(varbinary, a) = Convert(varbinary, UPPER(a))

Your solution doesn't work in all conditions. See below example..

create table #t (a varchar(100))

insert #t values ('Russell');
insert #t values ('russell');
insert #t values ('RUSSELL');
insert #t values ('HOLLYWOOD BACKSTORIES: DR. DOLITTLE goes behind the scenes of the hit comedy starring Eddie Murphy.')

Your Solution: SELECT * FROM #t WHERE Convert(varbinary, a) = Convert(varbinary, UPPER(a))
My Solution : select * from #t where a = UPPER(a) collate latin1_general_cs_as
drop table #t

It is becuase no size is given for varbinary

Try with

SELECT * FROM #t WHERE Convert(varbinary(1000), a) = Convert(varbinary(1000), UPPER(a))

This is the reason we should provide the size during the datatype convertion. See this
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

raky
Aged Yak Warrior

767 Posts

Posted - 2009-12-07 : 05:07:22

quote:
Originally posted by madhivanan

quote:
Originally posted by raky

quote:
Originally posted by russell

Not going to be terribly efficient if there are a lot of records but this will do it
create table #t (a varchar(10))

insert #t values ('Russell');
insert #t values ('russell');
insert #t values ('RUSSELL');

SELECT * FROM #t WHERE Convert(varbinary, a) = Convert(varbinary, UPPER(a))

Your solution doesn't work in all conditions. See below example..

create table #t (a varchar(100))

insert #t values ('Russell');
insert #t values ('russell');
insert #t values ('RUSSELL');
insert #t values ('HOLLYWOOD BACKSTORIES: DR. DOLITTLE goes behind the scenes of the hit comedy starring Eddie Murphy.')

Your Solution: SELECT * FROM #t WHERE Convert(varbinary, a) = Convert(varbinary, UPPER(a))
My Solution : select * from #t where a = UPPER(a) collate latin1_general_cs_as
drop table #t

It is becuase no size is given for varbinary

Try with

SELECT * FROM #t WHERE Convert(varbinary(1000), a) = Convert(varbinary(1000), UPPER(a))

This is the reason we should provide the size during the datatype convertion. See this
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

yes you are right madhivanan just now iam about to say that word by the time you posted about that..better to use varbinary(max)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-07 : 05:57:08

Why all the converts?

What's wrong with this?


create table #t (a varchar(10))

insert #t values ('Russell');
insert #t values ('russell');
insert #t values ('RUSSELL');

-- Russel
SELECT * FROM #t WHERE Convert(varbinary, a) = Convert(varbinary, UPPER(a))

-- Charlie
SELECT * FROM #t WHERE [a] NOT LIKE '%[abcdefghijklmnopqrstuvwxyz]%' COLLATE SQL_Latin1_General_Cp1_CS_AS

DROP TABLE #t

is the LIKE going to be any slower?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources