{"id":272,"date":"2013-03-15T10:46:01","date_gmt":"2013-03-15T10:46:01","guid":{"rendered":"https:\/\/sqlserverlearner.azurewebsites.net\/2013\/03\/15\/when-to-use-lowerupper-for-comparing-data-in-sql-server-alternatives\/"},"modified":"2013-03-15T10:46:01","modified_gmt":"2013-03-15T10:46:01","slug":"when-to-use-lowerupper-for-comparing-data-in-sql-server-alternatives","status":"publish","type":"post","link":"https:\/\/sqlserverlearner.com\/2013\/03\/15\/when-to-use-lowerupper-for-comparing-data-in-sql-server-alternatives\/","title":{"rendered":"When To Use LOWER\/UPPER for comparing data in SQL Server – Alternatives"},"content":{"rendered":"

In SQL Server LOWER<\/strong> Can be used to convert uppercase\/mixedcase string to lower case string. More about LOWER can be found here<\/a><\/p>\n

Similarly, UPPER<\/strong> Can be used to convert lowercase\/mixedcase string to upper case string. More about UPPER can be found here<\/a><\/p>\n

Example:<\/em>
\n[sql]
\nDECLARE @String VARCHAR(10)<\/p>\n

SET @String = ‘ABcDeFGH’<\/p>\n

SELECT @String STRING,LOWER(@String) LowerCaseSrting
\n[\/sql]<\/p>\n

Output:<\/em>
\n[text]
\nSTRING LowerCaseSrting
\n———- —————
\nABcDeFGH abcdefgh<\/p>\n

(1 row(s) affected)
\n[\/text]<\/p>\n

Comparing UpperCase and LowerCase strings
\n[sql]
\nDECLARE @String VARCHAR(10),@String1 VARCHAR(10)
\nSET @String = ‘ABcDeFGH’
\nSET @String1 = ‘abcdefgh’<\/p>\n

IF @String = @String1
\nPRINT ‘Equal’
\nELSE
\nPRINT ‘Not Equal’
\n[\/sql]<\/p>\n

Output:<\/em>
\n[text]
\nEqual
\n[\/text]
\nSQL Server by default creates databases with case insensitive collation. Hence we are getting output as Equal.<\/p>\n

Now if we create a database in case sensitive collation <\/em> and try the above query, we can notice that we get Not Equal <\/em> as output.<\/p>\n

Script:<\/em>
\n[sql]
\nCREATE DATABASE testLower COLLATE SQL_Latin1_General_CP1_CS_AS
\nGO<\/p>\n

USE testLower
\nGO<\/p>\n

DECLARE @String VARCHAR(10),@String1 VARCHAR(10)
\nSET @String = ‘ABcDeFGH’
\nSET @String1 = ‘abcdefgh’<\/p>\n

IF @String = @String1
\nPRINT ‘Equal’
\nELSE
\nPRINT ‘Not Equal’ <\/p>\n

[\/sql]<\/p>\n

Output:<\/em>
\n[text]
\nNot Equal
\n[\/text]<\/p>\n

The comparison will also fail when the tables are created with columns having case sensitive collation.<\/p>\n

Script:<\/em>
\n[sql]
\nCREATE TABLE TestTable(Value VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CI_AS)
\nGO
\nCREATE TABLE ReferTable(Value VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CI_AS)
\nGO<\/p>\n

INSERT INTO TestTable(Value)
\nSELECT ‘lowercase’
\nUNION ALL
\nSELECT ‘LowerCase’
\nUNION ALL
\nSELECT ‘LOWERCASE’
\nGO<\/p>\n

INSERT INTO ReferTable(Value)
\nSELECT ‘lowercase’
\nGO<\/p>\n

–This query gives all three rows
\n–No need of lower
\nSELECT * FROM TestTable T JOIN ReferTable R
\nON T.Value = R.Value<\/p>\n

———————————————————
\nCREATE TABLE TestTable1(Value VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CS_AS)
\nGO
\nCREATE TABLE ReferTable1(Value VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CS_AS)
\nGO<\/p>\n

INSERT INTO TestTable1(Value)
\nSELECT ‘lowercase’
\nUNION ALL
\nSELECT ‘LowerCase’
\nUNION ALL
\nSELECT ‘LOWERCASE’
\nGO<\/p>\n

INSERT INTO ReferTable1(Value)
\nSELECT ‘lowercase’
\nGO<\/p>\n

–This query gives only one row
\nSELECT * FROM TestTable1 T JOIN ReferTable1 R
\nON T.Value = R.Value
\n[\/sql]<\/p>\n

In such scenarios as discussed above, we have to specify LOWER along with the column names to compare UpperCase with Lower Case.<\/p>\n

[sql]
\n–Needs Lower to give all the tree rows
\nSELECT * FROM TestTable1 T JOIN ReferTable1 R
\nON LOWER(T.Value) = LOWER(R.Value)
\n[\/sql]<\/p>\n

Observations:<\/strong>
\nLOWER Can be ignored if the database and all the table columns that are specified in the query are in CASE INSENSITIVE Collation.<\/p>\n

Alternatives:<\/strong>
\nWe can specify the CASE INSENSITIVE Collation name with the column name to compare the column using Case insensitive Collation.
\n[sql]
\n–Alternative to LOWER\/UPPER
\n–Using CASE InSensitive Collation names in the query
\nSELECT * FROM TestTable1 T JOIN ReferTable1 R
\nON T.Value COLLATE SQL_Latin1_General_CP1_CI_AS =
\n R.Value COLLATE SQL_Latin1_General_CP1_CI_AS
\n[\/sql]<\/p>\n

Clean Up:<\/em>
\n[sql]
\nUSE master
\nGO<\/p>\n

DROP DATABASE testLower
\n[\/sql]<\/p>\n","protected":false},"excerpt":{"rendered":"

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: [sql] DECLARE @String VARCHAR(10) SET @String = ‘ABcDeFGH’ SELECT @String STRING,LOWER(@String) LowerCaseSrting…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2167,41],"tags":[],"class_list":["post-272","post","type-post","status-publish","format-standard","hentry","category-collations","category-sql-server-basics"],"_links":{"self":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/272","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/comments?post=272"}],"version-history":[{"count":0,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/272\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/media?parent=272"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/categories?post=272"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/tags?post=272"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}