how to check lower case - 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)

how to check lower case

Author

Topic

treeskin
Starting Member

22 Posts

Posted - 2006-08-14 : 03:14:44

I wanted to know how to check on lower case and use that to compare on the same field's value. Below tables simulator explains the request:

TableA.Field_A TableB.Field_A
a a
B b
a A
b b

Output will be row 2 and 3 to be show in the report. Main point is to get the same value but different case from both tables.

Thanks for the help.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-08-14 : 03:22:18

just use the LOWER (or UPPER) function in your comparison. btw, SQL server is normally case insensitive. I assume you have installed a case sensitive collation and that is why you need to do this.

-ec

treeskin
Starting Member

22 Posts

Posted - 2006-08-14 : 03:29:37

How to check if I installed case sensitive collation.

If to use LOWER or UPPER function, that will only make the value to be LOWER or UPPER, I have tested it out but don't getting the results I want.

My script already have a comparison like this: TableA.Field_A <> TableB.Field_A but what i want is to also pull out the results as row 2 and 3.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-14 : 03:56:54

Something like this?

SELECT		TableA.Field_A,
		TableB.Field_A
FROM		TableA
INNER JOIN	TableB ON LOWER(TableB.Field_A) = LOWER(TableA.Field_A)
WHERE		CAST(TableA.Field_A AS VARBINARY(100)) <> CAST(TableB.Field_A AS VARBINARY(100))

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-14 : 04:12:08

or simply this...

declare @xyz table
(
	a varchar(10),
	b varchar(10)
)

insert into @xyz 
select 'a', 'a' union all
select 'a', 'A' union all
select 'b', 'B'  union all
select 'b', 'b'

select a, b
from @xyz
where ascii(a)<>ascii(b)

Harsh Athalye
India.
"Nothing is Impossible"

treeskin
Starting Member

22 Posts

Posted - 2006-08-14 : 04:21:07

Hello Peter, it's not working as well :-(

In my SQL Server, it seems like treating 'A' is same as 'a' but it should be show as different.

treeskin
Starting Member

22 Posts

Posted - 2006-08-14 : 04:28:26

Hello Peter, I am so sorry, your script is actually working. As I forgot to remove my initial comparison (TableA.FieldA <> TableB.FieldA). So it's actually working!!

Hello Harsh, yours also working fine as well. Thanks.

Another way which just discover is as below:
CONVERT(varbinary(5), UPPER(TableA.FieldA)) <> CONVERT(varbinary(5), TableA.FieldA)) AND TableB.FieldA is not null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources