SQL - Find all UPPER CASE strings

Question

Is there a way i can get all the uppercase strings in column from a table. Like creating a function?

Is it possible to perform case-sensitive queries? In particular, I'd like to find all strings in a certain column that are in upper case. This is what I've tried:

select *
from MyTable
where 
column1 = upper(column1) collate SQL_Latin1_General_CP1_CS_AS

I think the approach in your example should work. Did you try it and if so, did it not work? — jpw, Aug 10 '15 at 18:01

score 3 · Accepted Answer · edited May 23 '17 at 10:28

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

Hi Jason, is there a way i can query the entire DB, and get it to return the table name and column name with the uppercase strings? — AndroidAL, Aug 11 '15 at 14:32
Can you explain more of what you are trying to do? Are you looking to search all fields? Or are you trying to search column names? — Jason Geiger, Aug 12 '15 at 15:20

score 0 · Answer 2 · answered Aug 10 '15 at 18:05

0

You can use HASHBYTES and compare the hash.

select *
  from MyTable
 where hashbytes('SHA1',upper(column1)) = hashbytes('SHA1',column1)

This will work even if your database is case-insensitive.

answered Aug 10 '15 at 18:05

Brian Stork

945
2
8
14

1

Yes it will work...but it is horribly inefficient. This forces the engine to execute this function twice for every single row. – Sean Lange Aug 10 '15 at 18:36

score -1 · Answer 3 · answered Aug 10 '15 at 19:28

-1

I think Brian Stork's response will be faster than mine. If you want it in a function and don't want the function to use HASHBYTES then you have do something like this...

CREATE FUNCTION F_IsUpper
(
    @S VARCHAR(MAX)
)
RETURNS BIT
AS
BEGIN
    DECLARE @Result AS BIT
    SET @Result = 0

    IF @S = UPPER(@S) COLLATE Latin1_General_CS_AS
    BEGIN
        SET @Result = 1
    END
    RETURN @Result
END
GO
SELECT * FROM MyTable WHERE DBO.F_IsUpper(column1)= 1

answered Aug 10 '15 at 19:28

Jason Geiger

1,912
18
32

I don't think this is an efficient way of doing things. I put this out there for educational purposes. The way you have it in the Main SQL statement above is probably correct. Please don't down vote because it has a negative impact on people who are trying to help's reputation. Instead post your own answer and it will get up voted. – Jason Geiger Aug 10 '15 at 19:30
1

I didn't downvote but bolstering a person's reputation for answers that are not helpful is not what the point is about. A downvote means the answer is not useful. While I don't necessarily think this isn't useful I question why it was posted at all if you already state you don't think it is very good. The other answer is itself a poor one from an efficiency standpoint and adds a lot of complexity for no benefit. The best way to do this is by the query the OP posted. – Sean Lange Aug 10 '15 at 20:18
@SeanLange We agree not to bolster one's reputation for unhelpful questions. However downvoting an answer with 0 reputation slightly hurts those that are trying to answer earnestly. Also a 0 vote answer still can still educational merit or help with explaining the question overall to new readers. – Jason Geiger Aug 12 '15 at 18:30
1

Sadly that isn't SO works. It is based on merit and not intention. This forum is not intended as a learning place. For the more constructive type of sql server forum you might take a look at http://www.sqlservercentral.com There the learning mentality is supported and encouraged. Here it is more about the answer and mentoring takes a back seat. – Sean Lange Aug 12 '15 at 18:44

SQL - Find all UPPER CASE strings

3 Answers3

Linked