SQL Return all upper case values in a column

Question

I have fields with values of a mix of both Upper case and Lower Case characters. I am trying to return just the Upper case values as one result and likewise return just the lower case values in another. I am not trying to convert one to the other, just return the current data as is.

I cant seem to find a statement to do this and "SUBSTRING" will only return the value I specify i.e. the first and last characters

So for example if I have AAbbCCdd and want to return the upper case values, the result I need is AACC.

Answer 1

With a function:

CREATE FUNCTION [dbo].[GetCased](@BUFFER VARCHAR(MAX), @GETUPPER BIT) RETURNS VARCHAR(MAX) AS
BEGIN
    DECLARE @LEN INT = LEN(@BUFFER), @POS INT = 1, @CHAR CHAR(1), @RESULT VARCHAR(MAX) = ''
    WHILE @POS <= @LEN BEGIN
        SET @CHAR = SUBSTRING(@BUFFER, @POS, 1)
        SET @RESULT += CASE WHEN @CHAR COLLATE Latin1_General_CS_AS = 
            CASE WHEN @GETUPPER = 1 THEN UPPER(@CHAR) ELSE LOWER(@CHAR) END COLLATE Latin1_General_CS_AS THEN @CHAR ELSE '' END
        SET @POS += 1
    END
    RETURN @RESULT
END

... 

select
  dbo.GetCased('AAbbCCdd', 1) as 'all upper',
  dbo.GetCased('AAbbCCdd', 0) as 'all lower'

Or

CREATE FUNCTION [dbo].[fnRemovePatternFromString](@BUFFER VARCHAR(MAX), @PATTERN VARCHAR(128)) RETURNS VARCHAR(MAX) AS
BEGIN
    DECLARE @POS INT = PATINDEX(@PATTERN, @BUFFER COLLATE Latin1_General_CS_AS)
    WHILE @POS > 0 BEGIN
        SET @BUFFER = STUFF(@BUFFER, @POS, 1, '')
        SET @POS = PATINDEX(@PATTERN, @BUFFER COLLATE Latin1_General_CS_AS)
    END
    RETURN @BUFFER
END

...

select
  dbo.fnRemovePatternFromString('AAbbCCdd ', '%[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%') as 'all lower'
  dbo.fnRemovePatternFromString('AAbbCCdd ', '%[abcdefghijklmnopqrstuvwxyz]%') as 'all upper'

(Cannot use [a-z])