check if lower case or higher case

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 2005 Forums

Transact-SQL (2005)

check if lower case or higher case

Author

Topic

Lambik
Starting Member

13 Posts

Posted - 2010-05-07 : 05:01:27

Hello,

I have a table with values which are or upper case or lower case.
When upper case I have to generate a Y when lower case i have to generate a N.

when I run sql the result set is always low
Has anybody an idea?

CREATE TABLE #TMP(
[id] [varchar](10) NOT NULL,
[Name] [varchar](50) NULL,
[Initial] [varchar](5) NULL,

)

insert into #TMP
select '1','John','K'
union
select '2','Eric','L'
union
select '3','Geert','k'
union
select '4','Peter','a'
union
select '5','Andre','X'

select * from #TMP

select cust.name
, cust.initial
, case (cust.initial)
when lower(cust.initial) then 'low'
when upper(cust.initial) then 'high'
else 'X'
End
from dbo.#TMP cust

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-05-07 : 05:06:56

Hi this will help you i hope

select cust.name
, cust.initial
, case
when cust.initial COLLATE Latin1_General_CS_AS=upper(cust.initial) then 'high'
else 'low'
End
from dbo.#TMP cust

Iam a slow walker but i never walk back

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-05-07 : 05:11:15

Since your table is not case-sensitive u have to either use the collate for column in where condition where u want check for case-sensitivity or else

alter table table1
ALTER COLUMN Column1 VARCHAR(20)
COLLATE Latin1_General_CS_AS

hope u understand the point

Refer this
http://blog.sqlauthority.com/2007/04/30/case-sensitive-sql-query-search/

Iam a slow walker but i never walk back

Lambik
Starting Member

13 Posts

Posted - 2010-05-07 : 05:22:14

ok add collate gives me the right returns
thx again dineshrajan

from the blog of Pinal Dave I understand that if you want to do a case sensitive search in a field you have to use collate. didn't knew that.

Lambik

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources