select records that contain capital letters

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)

select records that contain capital letters

Author

Topic

maryxu
Starting Member

36 Posts

Posted - 2008-04-08 : 13:44:27

how to find capital lettes in a field use query? thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-04-08 : 14:53:12

You can use a case sensitive collation like "COLLATE SQL_Latin1_General_CP1_CS_AS" to perform comparisons.

You're going to need to be more specific if you want a solution.
Do you want all rows in a table where a column contains any capital letters?
or where ALL letters are upper case?
Or maybe from a single value you want only the characters that are upper calse alpha characters returned?

This example will return only the row where ALL the letters are upper case:


select	f
from	(
	select 'tg' as f union all
	select 'Tg' union all
	select 'TG'
	) d
where	f COLLATE SQL_Latin1_General_CP1_CS_AS =  upper(f) COLLATE SQL_Latin1_General_CP1_CS_AS

Be One with the Optimizer
TG

hema_sundar
Starting Member

2 Posts

Posted - 2011-04-27 : 05:03:06

The answer was quite useful. But is there any way to find the caps in some words or some part of the word.

like China HEALTHCARE INVEStement.

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-27 : 05:32:12

This will help you
I just modified TG query see this he did good
To find all upper case, lower case and single or multiple upper case letter

create table #t(Fname nvarchar(max))
insert into #t
Select 'HI HOW ARE YOU' union all
select 'This is Word' union all
select 'hi India' union all
select 'thinkInda' union all
select 'HI' Union all
select 'India' union all
select 'think'

--To find all lower cases
Select * from #t
where Fname COLLATE SQL_Latin1_General_CP1_CS_AS = lower(Fname) COLLATE SQL_Latin1_General_CP1_CS_AS

--To find all upper cases
select * from #t
where Fname COLLATE SQL_Latin1_General_CP1_CS_AS = upper(Fname) COLLATE SQL_Latin1_General_CP1_CS_AS

--To find only single upper case
select * from #t
except
(Select * from #t
where Fname COLLATE SQL_Latin1_General_CP1_CS_AS = lower(Fname) COLLATE SQL_Latin1_General_CP1_CS_AS
union all
select * from #t
where Fname COLLATE SQL_Latin1_General_CP1_CS_AS = upper(Fname) COLLATE SQL_Latin1_General_CP1_CS_AS
)

Raghu' S

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-27 : 06:11:12

How about
select * from #t
where Fname COLLATE SQL_Latin1_General_CP1_CS_AS <> lower(Fname) COLLATE SQL_Latin1_General_CP1_CS_AS

That should give entries that contain capital letters.
What do you want to return?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

hema_sundar
Starting Member

2 Posts

Posted - 2011-04-27 : 06:39:06

Thanks, it was useful

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources