When To Use LOWER/UPPER for comparing data in SQL Server – Alternatives

Posted on : 15-03-2013 | By : Devi Prasad | In : Collations, SQL Server Basics

Share:

0


In SQL Server LOWER Can be used to convert uppercase/mixedcase string to lower case string. More about LOWER can be found here

Similarly, UPPER Can be used to convert lowercase/mixedcase string to upper case string. More about UPPER can be found here

Example:

DECLARE @String VARCHAR(10)

SET @String = 'ABcDeFGH'

SELECT @String STRING,LOWER(@String) LowerCaseSrting

Output:

STRING     LowerCaseSrting
---------- ---------------
ABcDeFGH   abcdefgh

(1 row(s) affected)

Comparing UpperCase and LowerCase strings

DECLARE @String VARCHAR(10),@String1 VARCHAR(10)
SET @String = 'ABcDeFGH'
SET @String1 = 'abcdefgh'

IF @String = @String1
PRINT 'Equal'
ELSE
PRINT 'Not Equal' 

Output:

Equal

SQL Server by default creates databases with case insensitive collation. Hence we are getting output as Equal.

Now if we create a database in case sensitive collation and try the above query, we can notice that we get Not Equal as output.

Script:

CREATE DATABASE testLower COLLATE SQL_Latin1_General_CP1_CS_AS
GO

USE testLower
GO

DECLARE @String VARCHAR(10),@String1 VARCHAR(10)
SET @String = 'ABcDeFGH'
SET @String1 = 'abcdefgh'

IF @String = @String1
PRINT 'Equal'
ELSE
PRINT 'Not Equal' 

Output:

Not Equal

The comparison will also fail when the tables are created with columns having case sensitive collation.

Script:

CREATE TABLE TestTable(Value VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CI_AS)
GO
CREATE TABLE ReferTable(Value VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CI_AS)
GO

INSERT INTO TestTable(Value)
SELECT 'lowercase'
UNION ALL
SELECT 'LowerCase'
UNION ALL
SELECT 'LOWERCASE'
GO

INSERT INTO ReferTable(Value)
SELECT 'lowercase'
GO


--This query gives all three rows
--No need of lower 
SELECT * FROM TestTable T JOIN ReferTable R
ON T.Value = R.Value

---------------------------------------------------------
CREATE TABLE TestTable1(Value VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CS_AS)
GO
CREATE TABLE ReferTable1(Value VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CS_AS)
GO

INSERT INTO TestTable1(Value)
SELECT 'lowercase'
UNION ALL
SELECT 'LowerCase'
UNION ALL
SELECT 'LOWERCASE'
GO

INSERT INTO ReferTable1(Value)
SELECT 'lowercase'
GO

--This query gives only one row
SELECT * FROM TestTable1 T JOIN ReferTable1 R
ON T.Value = R.Value

In such scenarios as discussed above, we have to specify LOWER along with the column names to compare UpperCase with Lower Case.

--Needs Lower to give all the tree rows
SELECT * FROM TestTable1 T JOIN ReferTable1 R
ON LOWER(T.Value) = LOWER(R.Value)

Observations:
LOWER Can be ignored if the database and all the table columns that are specified in the query are in CASE INSENSITIVE Collation.

Alternatives:
We can specify the CASE INSENSITIVE Collation name with the column name to compare the column using Case insensitive Collation.

--Alternative to LOWER/UPPER
--Using CASE InSensitive Collation names in the query 
SELECT * FROM TestTable1 T JOIN ReferTable1 R
ON T.Value COLLATE SQL_Latin1_General_CP1_CI_AS = 
      R.Value COLLATE SQL_Latin1_General_CP1_CI_AS

Clean Up:

USE master
GO

DROP DATABASE testLower
(Visited 113 times, 1 visits today)



References : Devi Prasad (sqlserverlearner.com)

Need Help On SQL Server?

Cannot Find Solution to your problem (or) If you are looking for some help on SQL Server. Dont worry Click Here to Post your question and solve your issue.


Do you like my blog?

If you liked reading this blog, please help spread the word by sharing this blog with your friends.




Write a comment