Test for Upper Case - T-Sql

Question

All,

How can I check if a specified varchar character or entire string is upper case in T-Sql? Ideally I'd like to write a function to test if a character is upper case, then I can later apply that to a generic varchar. It should return false for non alphabetic characters. I am only interested in english language characters.

I am working with T-sql in SQL Management Studio, and I have tried pulling records beginning with a lower case letter from a table in this fashion:

select * from TABLE
where SUBSTRING(author,1,1) != LOWER(SUBSTRING(author,1,1))

Which returns 0 records, but I know there are records beginning with upper and lower case letters.

Thanks

---

EDIT: Since both podiluska and joachim-isaksoon have successfully answered my question (Both methods work for my purposes), would someone mind explaining which would be the most efficient method to use to query a table with a large number of records to filter out records with authors beginning with or without a capital letter?

Answer 1

Using collations

eg:

if ('a'='A' Collate Latin1_General_CI_AI) 
    print'same 1'
else
    print 'different 1'

if ('a'='A' Collate Latin1_General_CS_AI) 
    print'same 2'
else
    print 'different 2' 

The CS in the collation name indicates Case Sensitive (and CI, Case Insensitive). The AI/AS relates to accent sensitivity.

or in your example

SUBSTRING(author,1,1) <> LOWER(SUBSTRING(author,1,1)) COLLATE Latin1_General_CS_AI

Answer 2

To check if ch is upper case, and is a character that can be converted between upper and lower case (ie excluding non alphabetic characters);

WHERE UNICODE(ch) <> UNICODE(LOWER(ch))

An SQLfiddle to test with;

Answer 3

something like

declare @v varchar(10) = 'ABC', @ret int = 0
select @ret = 1 where upper(@v)=@v COLLATE SQL_Latin1_General_CP1_CS_AS
select @ret

Answer 4

Lower case letters have the same ASCII or UNICODE value as their upper-case version with the exception that lower case letters have a bit flag of 32 set.

This is very easy to detect directly, or to wrap the detection into User-defined functions like IsUpper() and IsLower().

Example (note that both ASCII() and UNICODE() work interchangeably here):

DECLARE @Letter char(1);

SET @Letter = 'A'
PRINT   
    @Letter + space(1) + 
    CASE
    WHEN (UNICODE(@Letter) & 32 > 0) THEN 'is lower case'
    ELSE 'is UPPER CASE'
    END

SET @Letter = 'z'
PRINT   
    @Letter + space(1) + 
    CASE
    WHEN (ASCII(@Letter) & 32 > 0) THEN 'is lower case'
    ELSE 'is UPPER CASE'
    END

-- Output:
--    A is UPPER CASE
--    z is lower case

This function will look for any upper case letters in an entire string using a recursive CTE:

CREATE FUNCTION [dbo].[ContainsUpper](@InputString [varchar](80))
RETURNS [bit] WITH EXECUTE AS CALLER
AS 
BEGIN
    DECLARE @Result         bit     = 0,
            @LowerString    varchar(80) = lower(@InputString);

    WITH Letters
    AS
    (
        SELECT  Position    = 1,
                IsUpper     = 
                    CASE unicode(left(@InputString,1))
                    WHEN unicode(left(@LowerString,1)) THEN 0
                    ELSE 1
                    END

        UNION ALL

        SELECT  Position    = Position + 1,
                IsUpper     = 
                    CASE unicode(substring(@InputString, Position + 1, 1))
                    WHEN unicode(substring(@LowerString, Position + 1, 1)) THEN 0
                    ELSE 1
                    END
        FROM    Letters
        WHERE   Position < len(@InputString)
    )
    SELECT  @Result = max(convert(int, IsUpper))
    FROM    Letters

    RETURN @Result
END

Answer 5

3 easy steps to check string is uppercase or lowercase program in PL/SQL using both

       1.Function 
       2.procedure

using function

step:1 there are two variable

step:2 one is to stored your string and the other is to convert your string into uppercase

step:3 the check condition if variable_one != variable_convert then it show string is lowercase else show string is uppercase

set serveroutput on 
declare
        string_one varchar2(5);
        string_convert varchar2(5);
begin
        string_one:=&string;
        string_convert:=UPPER(string_one);
        if string_one != string_convert then
                dbms_output.put_line('string is lowercse');
        else
                dbms_output.put_line('string is UPPERCASE');
        end if;

end;
/

using procedure

check string is an uppercase or lowercase program in pl/sql using procedure logic is the same but no need to declare a variable change

 set serveroutput on 
            create or replace procedure upper_cse(str_one varchar2)
        is
    begin
            if str_one != UPPER(str_one) then
                    dbms_output.put_line('string is lowercse');
            else
                    dbms_output.put_line('string is UPPERCASE');
            end if;
    end;
    /