How to find rows that have a value that contains a lowercase letter
SqlMysqlSql Problem Overview
I'm looking for an SQL query that gives me all rows where ColumnX contains any lowercase letter (e.g. "1234aaaa5789"). Same for uppercase.
Sql Solutions
Solution 1 - Sql
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.
Solution 2 - Sql
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.
Solution 3 - Sql
SELECT * FROM Yourtable
WHERE UPPER([column_NAME]) COLLATE Latin1_General_CS_AS !=[Column_NAME]
Solution 4 - Sql
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)
Solution 5 - Sql
for search all rows in lowercase
SELECT *
FROM Test
WHERE col1
LIKE '%[abcdefghijklmnopqrstuvwxyz]%'
collate Latin1_General_CS_AS
Thanks Manesh Joseph
Solution 6 - Sql
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
Solution 7 - Sql
I've done something like this to find out the lower cases.
SELECT *
FROM YourTable
where BINARY_CHECKSUM(lower(ColumnName)) = BINARY_CHECKSUM(ColumnName)
Solution 8 - Sql
mysql> SELECT '1234aaaa578' REGEXP '^[a-z]';
Solution 9 - Sql
I have to add BINARY to the ColumnX, to get result as case sensitive
SELECT * FROM MyTable WHERE BINARY(ColumnX) REGEXP '^[a-z]';
Solution 10 - Sql
I'm not an expert on MySQL I would suggest you look at REGEXP.
SELECT * FROM MyTable WHERE ColumnX REGEXP '^[a-z]';
Solution 11 - Sql
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,
Solution 12 - Sql
--For Sql
SELECT *
FROM tablename
WHERE tablecolumnname LIKE '%[a-z]%';
Solution 13 - Sql
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]