How to pull records containing lower characters

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 pull records containing lower characters

Author

Topic

treeskin
Starting Member

22 Posts

Posted - 2006-04-13 : 08:44:36

I have records such as below:

Field1
=======
ABC
abC
AbC

How to pull out the records that contain lower case? I found LOWER Substring but that is not what I want. The result should come out abC and AbC.

Please help.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-13 : 09:04:07

[code]Create table #t (F varchar(5))
Insert into #t values('ABc')
Insert into #t values('ABC')
Insert into #t values('aBC')
Insert into #t values('AbC')
Insert into #t values('abC')
Insert into #t values('PQ')
Insert into #t values('pqR')
Insert into #t values('Z')
Insert into #t values('AAAAd')
Insert into #t values('LLB')
Insert into #t values('BSc')

Select * from #t where convert(varbinary(10),Upper(F)) != convert(varbinary(10),F)

Drop table #t[/code]

Edit : More sample data added for clarity

Srinika

russellsoft

9 Posts

Posted - 2006-04-13 : 09:09:13

You can use UNICODE function that returns the integer value for char...

The follow code will display
65
97


declare @lChar char
set @lChar = 'A'
select UNICODE(@lChar)
set @lChar = 'a'
select UNICODE(@lChar)

It is mean then for 'A' - unicode is 65 and for 'a' - 97

For lower case letter this value will be always less then for upper case... So you can use it how do you can... Just I think that I didn't understood what do you exactly want...

treeskin
Starting Member

22 Posts

Posted - 2006-04-13 : 09:11:13

Hi Srinika, thanks but your above query has the result as ABC. I would need the result to be abC and AbC.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-13 : 09:15:15

Try it again (Copy & paste - execute) -- I corrected a typo error

Srinika

treeskin
Starting Member

22 Posts

Posted - 2006-04-13 : 09:22:53

hmm.. let me rephrase my question.

Records as below:
Supp_name Supp_Address
========= ============
AbC California
ABC Texas
aBc Las Vegas

Need to pull out Supp_name that contains lower characters in between as per below result.

Result:
Supp_name Supp_Address
========== ============
AbC California
aBc Las Vegas

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-13 : 09:32:45

I'm writing the same code here with ur field names.
(Copy & paste - execute and c the results)

Create table #t (Supp_name varchar(5),Supp_Address varchar(15) )
Insert into #t values('AbC', 'California')
Insert into #t values('ABC', 'Texas')
Insert into #t values('aBc', 'Las Vegas')

Select * from #t where convert(varbinary(10),Upper(Supp_name)) != convert(varbinary(10),Supp_name)

Drop table #t

Srinika

Kristen
Test

22859 Posts

Posted - 2006-04-13 : 09:51:11

I find this syntax a little easier because you don;t have to worry abou column sizes in the CONVERT to varbinary etc:

SELECT * FROM #t WHERE UPPER(Supp_name) <> Supp_name COLLATE Latin1_General_BIN

Kristen

treeskin
Starting Member

22 Posts

Posted - 2006-04-13 : 09:59:11

Srinika, yup.. your code is working fine in mine... thanks. :-)

Kristen, where does Latin1_General_BIN came from?

Kristen
Test

22859 Posts

Posted - 2006-04-13 : 10:01:04

"where does Latin1_General_BIN came from?"

Its a binary collation, and thus case sensitive.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources