CODEWITHSUNDEEP
sqlmysql
ripper234
ripper234

Reputation: 230048

How to find rows that have a value that contains a lowercase letter

I'm looking for an SQL query that gives me all rows where ColumnX contains any lowercase letter (e.g. "1234aaaa5789"). Same for uppercase.

Upvotes: 105

Views: 198413

Answers (15)

user1917451
user1917451

Reputation: 169

This worked for me

SELECT * FROM programs WHERE LOWER(CODE) <> CAST(CODE AS BINARY)

Upvotes: 0

user20504274
user20504274

Reputation: 11

This works in Firebird SQL, it should work in any SQL queries I believe, unless the underlying connection is not case sensitive.

To find records with any lower case letters:

select * from tablename where upper(fieldname) <> fieldname

To find records with any upper case letters:

select * from tablename where lower(fieldname) <> fieldname

Upvotes: 1

Robert Patterson
Robert Patterson

Reputation: 520

Logically speaking Rohit's solution should have worked, but it didn't. I think SQL Management Studio messed up when trying to optimize this.

But by modifying the string before comparing them I was able to get the right results. This worked for me:

SELECT [ExternalId]
FROM [EquipmentSerialsMaster] where LOWER('0'+[ExternalId]) COLLATE Latin1_General_CS_AS != '0'+[ExternalId]

Upvotes: 1

Mahib
Mahib

Reputation: 4063

I've done something like this to find out the lower cases. 

SELECT *
FROM YourTable
  where BINARY_CHECKSUM(lower(ColumnName)) = BINARY_CHECKSUM(ColumnName)

Upvotes: 7

martincarlin87
martincarlin87

Reputation: 11042

This is how I did it for utf8 encoded table and utf8_unicode_ci column, which doesn't seem to have been posted exactly:

SELECT *
FROM table
WHERE UPPER(column) != BINARY(column)

Upvotes: 28

Jose Carlos Ramos Carmenates
Jose Carlos Ramos Carmenates

Reputation: 2943

I have to add BINARY to the ColumnX, to get result as case sensitive

SELECT * FROM MyTable WHERE BINARY(ColumnX) REGEXP '^[a-z]';

Upvotes: 3

user7912960
user7912960

Reputation: 19

--For Sql

SELECT *
FROM tablename
WHERE tablecolumnname LIKE '%[a-z]%';

Upvotes: 0

Nicolas Finelli
Nicolas Finelli

Reputation: 2178

In Posgresql you could use ~

For example you could search for all rows that have col_a with any letter in lowercase

select * from your_table where col_a '[a-z]';

You could modify the Regex expression according your needs.

Regards,

Upvotes: 1

Devraj Gadhavi
Devraj Gadhavi

Reputation: 3611

SELECT * FROM my_table WHERE my_column = 'my string'
COLLATE Latin1_General_CS_AS

This would make a case sensitive search.

EDIT

As stated in kouton's comment here and tormuto's comment here whosoever faces problem with the below collation

COLLATE Latin1_General_CS_AS

should first check the default collation for their SQL server, their respective database and the column in question; and pass in the default collation with the query expression. List of collations can be found here.

Upvotes: 56

El David
El David

Reputation: 666

for search all rows in lowercase

SELECT *
FROM Test
WHERE col1 
LIKE '%[abcdefghijklmnopqrstuvwxyz]%'
collate Latin1_General_CS_AS

Thanks Manesh Joseph

Upvotes: 15

NBS
NBS

Reputation: 479

SELECT * FROM Yourtable 
WHERE UPPER([column_NAME]) COLLATE Latin1_General_CS_AS !=[Column_NAME]

Upvotes: 46

subhash
subhash

Reputation: 286

IN MS SQL server use the COLLATE clause.

SELECT Column1
FROM Table1
WHERE Column1 COLLATE Latin1_General_CS_AS = 'casesearch'

Adding COLLATE Latin1_General_CS_AS makes the search case sensitive.

Default Collation of the SQL Server installation SQL_Latin1_General_CP1_CI_AS is not case sensitive.

To change the collation of the any column for any table permanently run following query.

ALTER TABLE Table1
ALTER COLUMN Column1 VARCHAR(20)
COLLATE Latin1_General_CS_AS

To know the collation of the column for any table run following Stored Procedure.

EXEC sp_help DatabaseName

Source : SQL SERVER – Collate – Case Sensitive SQL Query Search

Upvotes: 9

geon
geon

Reputation: 8479

SELECT * FROM my_table 
WHERE UPPER(some_field) != some_field

This should work with funny characters like åäöøüæï. You might need to use a language-specific utf-8 collation for the table.

Upvotes: 131

CJM
CJM

Reputation: 12016

I'm not an expert on MySQL I would suggest you look at REGEXP.

SELECT * FROM MyTable WHERE ColumnX REGEXP '^[a-z]';

Upvotes: 1

Elzo Valugi
Elzo Valugi

Reputation: 27866

mysql> SELECT '1234aaaa578' REGEXP '^[a-z]';

Upvotes: 3

Related Questions