How to Find Values in All Caps in SQL Server

How to find values in all caps in SQL Server?

You can force case sensitive collation;

select * from T
  where fld = upper(fld) collate SQL_Latin1_General_CP1_CS_AS

SQL - Find all UPPER CASE strings

You nailed it the first time.

SELECT * FROM MyTable WHERE Column1 = UPPER(Column1) COLLATE SQL_Latin1_General_CP1_CS_AS

The above is the simplest and appears to be the fastest. It would slow down by putting it into a function and now builtin function exists. The other answers are worth their merit for explanation reasons.

Edit:
Part 2 - The original questioner further asked "How do I search all tables & columns in the database?". Here is a quick way to find. If you want to return all fields that have all capitals simply remove "TOP 1" from the procedure below but beware. If you have more than a lot of records you will probably run out of memory.

CREATE PROCEDURE SP_SearchAllTablesForAFieldWithAllCapitals
AS
BEGIN
    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128)
    SET  @TableName = ''

    WHILE @TableName IS NOT NULL
    BEGIN
        SET @ColumnName = ''
        SET @TableName = 
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM    INFORMATION_SCHEMA.TABLES
            WHERE       TABLE_TYPE = 'BASE TABLE'
                AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )

        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM    INFORMATION_SCHEMA.COLUMNS
                WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND TABLE_NAME  = PARSENAME(@TableName, 1)
                    AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                    AND QUOTENAME(COLUMN_NAME) > @ColumnName
            )

            IF @ColumnName IS NOT NULL
            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'SELECT TOP 1''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                    FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' = UPPER(' + @ColumnName + ') COLLATE Latin1_General_CS_AS'
                )
            END
        END 
    END

    SELECT ColumnName, ColumnValue FROM #Results
END
GO
EXEC SP_SearchAllTablesForAFieldWithAllCapitals

FYI: I used the query from here as a starting point.
How to search all text fields in a DB for some substring with T-SQL

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

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.

Locate upper case characters in SQL Server database field

You can do a binary comparison using:

select *
from Cust
where cast(Surname as varbinary(120)) != cast(lower(Surname) as varbinary(120))

Select ALL fields that contains only UPPERCASE letters

You may want to use a case sensitive collation. I believe the default is case insensitive. Example:

CREATE TABLE my_table (
   id int,
   name varchar(50)
) CHARACTER SET latin1 COLLATE latin1_general_cs;

INSERT INTO my_table VALUES (1, 'SomeThing');
INSERT INTO my_table VALUES (2, 'something');
INSERT INTO my_table VALUES (3, 'SOMETHING');
INSERT INTO my_table VALUES (4, 'SOME4THING');

Then:

SELECT * FROM my_table WHERE name REGEXP '^[A-Z]+$';
+------+-----------+
| id   | name      |
+------+-----------+
|    3 | SOMETHING |
+------+-----------+
1 row in set (0.00 sec)

If you don't want to use a case sensitive collation for the whole table, you can also use the COLLATE clause as @kchau suggested in the other answer.

Let's try with a table using a case insensitive collation:

CREATE TABLE my_table (
   id int,
   name varchar(50)
) CHARACTER SET latin1 COLLATE latin1_general_ci;

INSERT INTO my_table VALUES (1, 'SomeThing');
INSERT INTO my_table VALUES (2, 'something');
INSERT INTO my_table VALUES (3, 'SOMETHING');
INSERT INTO my_table VALUES (4, 'SOME4THING');

This won't work very well:

SELECT * FROM my_table WHERE name REGEXP '^[A-Z]+$';
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | SomeThing |
|    2 | something |
|    3 | SOMETHING |
+------+-----------+
3 rows in set (0.00 sec)

But we can use the COLLATE clause to collate the name field to a case sensitive collation:

SELECT * FROM my_table WHERE (name COLLATE latin1_general_cs) REGEXP '^[A-Z]+$';
+------+-----------+
| id   | name      |
+------+-----------+
|    3 | SOMETHING |
+------+-----------+
1 row in set (0.00 sec)

SQL Select values starting with a capital letter

This is a bit of a pain. You can use ASCII() or COLLATE, but these depend on how the data is stored. For varchar() and char(), this will work:

where ASCII(left(col1, 1)) between ASCII('A') and ASCII('Z')

---

Related Topics