upper/lowercase? 1

[CRuser89]

Hello,

I have the following select statement:

select a.name, a.address, a.telephone, b.jobtitle
from person a, job b
where a.jobtitleid = b.jobtitleid
and b.name = "jack"

How can I change it so that it looks for all cases of the name (i.e jack, Jack, JACK, etc...) Can I do the following or is there a better way to do this?:

and lower(b.name) = "jack"


Thanks,
Kathy

 

[AlexCuse]

Try this out, it should shed some light on your problem:

create table #TEMP (nm varchar (4))

insert into #TEMP
select 'Jack'
union all select 'jack'
union all select 'JACK'

select * from #TEMP where nm = 'jack'

select * from #TEMP

drop table #TEMP

This will help show you that you are actually looking for all cases when searching for nm = 'jack'

Hope this helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[CRuser89]

Thanks Alex....I used to work with oracle and we always had to use lower/upper for user parameters. I thought it would be the same for sql server. Thanks!!!

 

[gmmastros]

Case sensitive is determined by the collation you are using. To expand on Alex's query...

create table #TEMP (nm varchar (4) [!]collate SQL_Latin1_General_CP1_CS_AS[/!] )

insert into #TEMP
select 'Jack'
union all select 'jack'
union all select 'JACK'

select * from #TEMP where nm = 'jack'
select * from #TEMP where nm [!]collate SQL_Latin1_General_CP1_CI_AS [/!]= 'jack'

select * from #TEMP

drop table #TEMP

And, taking a closer look at the collation.

collate SQL_Latin1_General_CP1_[!]CI[/!]_AS

The part in red determines the case sensitivity.
CI = Case Insensitive
CS = Case Sensitive

Bottom line... Put collate SQL_Latin1_General_CP1_CI_AS in your query

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Unless, of course, you are already using a case insensitive collation. If you are, then you don't need to do anything, as Alex suggests.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom