checking for lowercase = uppercase

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

Transact-SQL (2008)

checking for lowercase = uppercase

Author

Topic

nickstje
Starting Member

1 Post

Posted - 2011-04-26 : 08:45:34

Hi there,I have a table which has a field called Number.

this Number is a string and I'm trying to find semi-duplicate Numbers.

With semi-duplicate I mean 'a505', 'A505', 'abA45','ABa45'

Any idea how to get those records?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-26 : 08:54:34

If you have case-sensitive collation and you want to ignore difference in case, you could use the SQL upper function as in


if (upper(col1) = upper(col2))

If you have case-insensitive collation and you want to distinguish difference in case, you could cast to varbinary and compare, as in


if (cast(col1 as varbinary) = cast(col2 as varbinary))

jacobbuter
Starting Member

1 Post

Posted - 2011-05-17 : 15:03:46

create table testje (test varchar(50));

insert into testje values ('A110')
insert into testje values ('a110')
insert into testje values ('A120')
insert into testje values ('a110')
insert into testje values ('A110')
insert into testje values ('a130')

select * from testje
where test like '[aA]110'

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-17 : 18:48:30

using Jacob's sample data, modified

create table #testje (test varchar(50));

insert into #testje values ('A110')
insert into #testje values ('a110')
insert into #testje values ('A120')
insert into #testje values ('aBa110')
insert into #testje values ('AbA110')
insert into #testje values ('a130')
SELECT *
FROM
(
select
test,
[UpperTest] = upper(substring(test,1,patindex('%[^A-Z]%' ,test) -1 ))
+substring(test,patindex('%[^A-Z]%' ,test) ,100)
,count(*) over(partition by upper(substring(test,1,patindex('%[^A-Z]%' ,test) -1 ))
+substring(test,patindex('%[^A-Z]%' ,test) ,100)) as row
from #testje
)t

where t.row > 1

Jim

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-18 : 08:02:52

This is less busy

select t2.test collate Latin1_General_BIN2
from #testje t1

CROSS APPLY #testje t2
where t1.test = t2.test
group by t2.test collate Latin1_General_BIN2
having count(*) > 1

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources