Use of Upper function in SQL query

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

SQL Server Development (2000)

Use of Upper function in SQL query

Author

Topic

rameshkg
Starting Member

22 Posts

Posted - 2004-07-27 : 00:20:07

Hi,

I need to write an SQL query which has a requirement as follows :

SELECT all the records with column3='AXA'. Here column3 is char type.

The SQL is a prepared statement and gets executed at run time.

The problem is column3 can have 'AXA' in lowercase also as 'axa'.

How to write an SQL, which, at run time picks up records with column3 can have mixed cases such as AXA or Axa or axa or AXa etc.,

Your help is appreciated.

Regards
Ramesh

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-27 : 00:44:15

SELECT column1, column2, column3
FROM table
WHERE UPPER(column3) = 'AXA'

Note, that this will no longer use an index when you do this. You really should just fix the data instead. You also might wnat to look at a computed column with an index if you have to do this a lot and there's lots of records.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-07-27 : 00:45:49

One way is to:

SELECT fields...
FROM table...
WHERE UPPER(fieldName) = UPPER(@param)

The other way is to turn off case sensitivity for the query, but I can't remember off hand how to do it.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-27 : 00:52:41

That's always messy. It doesn't use the indexes either. :)



USE Northwind

DECLARE @param VARCHAR(55)
SELECT @param = 'alf'

SELECT CompanyName
FROM Customers
WHERE CompanyName COLLATE Latin1_General_CI_AS LIKE @param + '%'

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

rameshkg
Starting Member

22 Posts

Posted - 2004-07-27 : 00:58:17

Thanks a lot for the reply.

However, when i execute the below query in query analyser, it is retrieving all the rows, irrespective of their case !!

The query is SELECT * FROM TABLE1 WHERE COL3='AXA';

This query retrieved even records with col3='axa' or 'AxA' etc.,

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-27 : 01:41:48

Look up Collate or collation.

This probably does what you are looking for though:

[CODE]
create table #test(iden INT Identity(1,1), descr varchar(10), col3 varchar(10))
insert into #test VALUES('NO','axa')
insert into #test VALUES('NO', 'AxA')
insert into #test VALUES('YES', 'AXA')
insert into #test VALUES('NO', 'AXa')
insert into #test VALUES('NO','axa')
insert into #test VALUES('NO','AxA')
insert into #test VALUES('NO','AXa')
insert into #test VALUES('YES','AXA')
insert into #test VALUES('NO','axa')

select *
from #test
where col3 = 'AXA' COLLATE SQL_EBCDIC037_CP1_CS_AS
[/CODE]

Duane.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-27 : 07:23:57

The second one I gave him should work actually. Yours will still not return everything if this is a case-sensitive server, which it should in this case. Here is you example using what I said earlier.


create table #test(iden INT Identity(1,1), descr varchar(10), col3 varchar(10))
insert into #test VALUES('NO','axa')
insert into #test VALUES('NO', 'AxA')
insert into #test VALUES('YES', 'AXA')
insert into #test VALUES('NO', 'AXa')
insert into #test VALUES('NO','axa')
insert into #test VALUES('NO','AxA')
insert into #test VALUES('NO','AXa')
insert into #test VALUES('YES','AXA')
insert into #test VALUES('NO','axa')


select * 
from #test
where col3 COLLATE Latin1_General_CI_AS  = 'AXA'

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-27 : 07:28:20

Yes,
You are right Derrick.

I also did not notice the use of collate in your second example.

Silly me :)

Duane.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-27 : 07:57:23

Silly me again.

Derrick I tested mine on one of our case-insensitive servers and mine worked too :)

Duane.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-27 : 08:09:38

That's weird. I just tested it here on mine and it didn't work. What's your exact collation/sort order on that server?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-27 : 08:36:10

Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data

Duane.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-27 : 08:48:06

hmmmm, I'm using Latin1_General_CP1_CI_AS.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources