Simple q! [:D] - 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)

Simple q! [:D]

Author

Topic

Tim F
Starting Member

35 Posts

Posted - 2005-04-21 : 07:20:03

Hi all,

I've got a select statement that needs to return only records which are in caps (i.e. UPPER).
This below is wrong but I think I want something like it????

select * from person
where UNICODE(surname) = UNICODE(UPPER(surname))

Cheers All, Tim

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2005-04-21 : 08:20:09

Firstly, get the collation of your datebase (where the table is held) :


select databasepropertyex('yourDatabase', 'collation')

e.g. SQL_Latin1_General_CP1_CS_AS

then do something like this


select * from person
where UPPER(surname) = Surname collate SQL_Latin1_General_CP1_CS_AS

Notice the change in the second statment, I changed the collation to be case sensetive.

Hope this helps.

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destruction

Tim F
Starting Member

35 Posts

Posted - 2005-04-21 : 08:38:05

Thanks Matey!

Tim F
Starting Member

35 Posts

Posted - 2005-04-21 : 08:45:57

Oh, yeah, how to I get the sql_Latin_general_CP1 etc. into a variable? I don't want to hard code this. Thanks, Tim

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2005-04-21 : 08:55:16

quote:
Originally posted by Tim F

Oh, yeah, how to I get the sql_Latin_general_CP1 etc. into a variable? I don't want to hard code this. Thanks, Tim

Something like :


declare @collation as varchar(50)

set @collation =  convert(varchar, databasepropertyex('yourDatebase, 'collation'))

exec ('select * from person where upper(surname) = surname collate ' + @collation)

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destruction

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources