SQL Server Portal

A few days ago, I was working on list of customers and all the customers’ names were in upper case. Coincidentally, I found one customer having upper and lower case in his name. So, I thought of checking the entire customer list if anyone was having name in upper and lower case.
Note : The database is not case sensitive.

Let me create a sample to demonstrate the solution.

USE tempdb
GO
DROP TABLE tbl_sample
GO
CREATE TABLE tbl_sample
(
[ID] INT,
[Name] varchar(50)
)
GO
INSERT INTO tbl_sample VALUES (1,'RARESQL.COM')
INSERT INTO tbl_sample VALUES (2,'RaReSql.com')
INSERT INTO tbl_sample VALUES (3,'Raresql')
INSERT INTO tbl_sample VALUES (4,'raresql.com')
GO
SELECT * FROM tbl_sample
--OUTPUT

SOLUTION 1 : Using UPPER (String) Function

USE tempdb
GO
SELECT * FROM tbl_sample WHERE
UPPER([NAME]) COLLATE Latin1_General_CS_AS !=[NAME]
--OUTPUT

SOLUTION 2 : Using PATINDEX (String) Function

USE tempdb
GO
SELECT * FROM tbl_sample
WHERE PATINDEX('%[abcdefghijklmnopqrstuvwxyz]%'
,[NAME] COLLATE Latin1_General_CS_AS)>0
GO
--OUTPUT

SOLUTION 3 : Using ASCII(String) Function
In this solution, it will not only give you the rows having lower case letter but it will also give you what lower characters are there in those rows. Given below is the script.

USE tempdb
GO
--DROP FUNCTION dbo.[UDF_Extract_small_letters_From_String]
--GO
CREATE FUNCTION dbo.[UDF_Extract_small_letters_From_String]
(
@String VARCHAR(MAX)  -- Variable for string
)
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @RETURN_STRING VARCHAR(MAX)

;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
FROM N3 AS X, N3 AS Y)

SELECT @RETURN_STRING=ISNULL(@RETURN_STRING,'')
+ SUBSTRING(@String,Nums.n,1)
FROM N4 Nums
WHERE Nums.n<=LEN(@String)
AND ASCII(SUBSTRING(@String,Nums.n,1)) BETWEEN 97 AND 122

RETURN @RETURN_STRING
END
GO

SELECT *,dbo.[UDF_Extract_small_letters_From_String]([NAME])
As [Lower cases letters]
FROM tbl_sample
--OUTPUT