Sql to Find Upper Case Words from a Column

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

Find column values where first letter is upper case

You might try something like the following (assuming you've not done anything to make your SQL queries case-insensitive):

SELECT eid, ename
  FROM emp_info
 WHERE ename >= 'A'
   AND ename < CHR(ASCII('Z')+1);

This will ensure that the first character of ename falls between A and Z inclusive. The value of CHR(ASCII('Z')+1) is [ but that's not terribly important - I think it's clearer to use the functions in this case than the "magic character".

EDIT: The reason this works is that lower-case characters, as a group, appear after upper-case characters in many character sets*, so as long as a value of ename is between A and Z inclusive, according to the typical string comparison, it will start with an upper-case character.

*For example, here is the Unicode character table and here is the ASCII character table. I suspect that this solution may not work with EBCDIC character sets but I don't have a server handy on which I can confirm that suspicion.

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))

How to get only Capital letters from given value

This code may help you..

 declare @input as varchar(1000) -- Choose the appropriate size
  declare @output as varchar(1000) -- Choose the appropriate size

 select @input = 'Investigations and Remedial Measures', @output = ''

 declare @i int

select @i = 0

while @i < len(@input)
begin
select @i = @i + 1

select @output = @output +  case when  unicode(substring(@input, @i, 1))between 65 
   and 90 then substring(@input, @i, 1)  else '' end

end

SELECT @output

Select where column contains three uppercase letters in a row

Not really any regex support in SQL Server natively unless you want to install custom CLR objects. If your data is stored as case insensitive and you want to perform case sensitive searches, one way is to use the COLLATE clause against the column.

DECLARE @x TABLE(i int, surname nvarchar(500));

INSERT @x(i, surname) VALUES
    (1, 'this is not a match'),
    (2, 'this is a MATCH'),
    (3, 'this is not a match'),
    (4, 'this is DEFINITELY a match');
    
DECLARE @min int = 3;

SELECT i, surname
  FROM @x
  WHERE surname COLLATE Latin1_General_BIN2 
  LIKE N'%' + REPLICATE(N'[A-Z]', @min) + N'%';

Results:

i      surname
----   -----------------------------
2      this is a MATCH
4      this is DEFINITELY a match

This dbfiddle also demonstrates other values for @min (in case you want to identify 4, or 40, or 300 consecutive upper-case characters in a row).

Note this won't perform well, so hopefully it's not something you're doing often and at scale. Also it won't find a surname with other characters between the upper-case characters, like Van DE Moor or MC-Adams. Not that those are normal, but data isn't normal, and want you to understand the bill of goods.

SQL Select only the words that are in Capital

DECLARE @searchtext VARCHAR(100) = 'THIS SENTENCE IS IN UPPERCASE and this in lower case'
DECLARE @i INT = 1, @l INT = LEN(@searchtext)

WHILE (@i <= @l AND 1 = CHARINDEX(UPPER(LEFT(@searchtext,@i)),@searchtext COLLATE Latin1_General_CS_AS))
BEGIN
    SET @i = @i+1
END

SELECT RTRIM(LEFT(@searchtext, @i-1))

I can't get it to work with PATINDEX btw., no matter where I put the collation info.

---

Related Topics