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

 

BETWEEN - Testing Value in a Range in SQL Server

Performing Comparison on Date and Time Values in SQL Server

Boolean Values and Logical Operations in SQL Server Transact-SQL

⇑⇑ SQL Server Transact-SQL Tutorials

2017-01-21, 1462🔥, 0💬