Performing Comparison on Character Strings in SQL Server

Q

How To Perform Comparison on Character Strings in SQL Server Transact-SQL?

✍: FYIcenter.com

A

Comparison operations on character strings are performed based on the associated collation. Each collation defines rules on how characters are ordered, how character cases and accents are treated, etc. The tutorial exercise below shows you some character string comparison examples using the default collation: SQL_Latin1_General_CP1_CI_AS.

-- Case insensitive
DECLARE @x VARCHAR(40), @y VARCHAR(40);
SET @x = 'FYIcenter.com';
SET @y = 'fyicenter.COM';
SELECT CASE WHEN @x = @y THEN 'True' ELSE 'False' END;
GO
True

-- digits has less values than letters
DECLARE @x VARCHAR(40), @y VARCHAR(40);
SET @x = '1234';
SET @y = 'abcd';
SELECT CASE WHEN @x < @y THEN 'True' ELSE 'False' END;
GO
True

-- Trailing spaces are ignored
DECLARE @x VARCHAR(40), @y VARCHAR(40);
SET @x = 'FYIcenter.com';
SET @y = 'fyicenter.COM   ';
SELECT CASE WHEN @x = @y THEN 'True' ELSE 'False' END;
GO
True

-- Longer strings have greater values
DECLARE @x VARCHAR(40), @y VARCHAR(40);
SET @x = 'abcd';
SET @y = 'abcde';
SELECT CASE WHEN @x < @y THEN 'True' ELSE 'False' END;
GO
True

 

Boolean Values and Logical Operations in SQL Server Transact-SQL

⇒⇒SQL Server Transact-SQL Tutorials

2017-01-21, 251👍, 0💬