Edit

= (String comparison or assignment)

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

Compares two strings in a WHERE or HAVING clause or sets a variable or column to string or result of a string operation on the right side of the equation. For example, if a variable @x equals 'Adventure', then WHERE @x = 'Adventure' compares original value of @x is equal to the string value 'Adventure' exactly. Also you can use the = operator as an assignment operator. For example, you can call SET @a = 'AdventureWorks'.

Syntax

expression = expression

Arguments

expression

Specifies any valid expression of any one of the data types in the character and binary data type category, except the image, ntext, or text data types. Both expressions must be of the same data type, or one expression must be able to be implicitly converted to the data type of the other expression.

An explicit conversion to character data with CONVERT, or CAST must be used when comparing or assigning binary strings and any characters between the binary strings.

Remarks

String comparison using the = operator assumes that both strings are identical. For partial string comparison options, refer to the LIKE operator, or the CONTAINS and CONTAINSTABLE full text predicates.

The SQL Server Database Engine follows the ANSI/ISO SQL-92 specification (Section 8.2, Comparison Predicate, General rules #3) on how to compare strings with spaces. The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them. The padding directly affects the semantics of WHERE and HAVING clause predicates and other Transact-SQL string comparisons. For example, Transact-SQL considers the strings 'abc' and 'abc ' to be equivalent for most comparison operations. The only exception to this rule is the LIKE predicate. When the right side of a LIKE predicate expression features a value with a trailing space, the Database Engine doesn't pad the two values to the same length before the comparison occurs. Because the purpose of the LIKE predicate, by definition, is to facilitate pattern searches rather than simple string equality tests, this predicate doesn't violate the section of the ANSI SQL-92 specification mentioned earlier.

The SET ANSI_PADDING setting doesn't affect whether the Database Engine pads strings before it compares them. SET ANSI_PADDING only affects whether trailing blanks are trimmed from values being inserted into a table, so it affects storage but not comparisons.

Examples

A. Compare strings in a WHERE clause

SELECT LastName,
    FirstName
FROM Person.Person
WHERE LastName = 'Johnson';

B. Compare strings in a WHERE clause using conversion from binary

DECLARE @LNameBin BINARY (100) = 0x5A68656E67;

SELECT LastName,
    FirstName
FROM Person.Person
WHERE LastName = CONVERT(VARCHAR, @LNameBin);

C. String assignment to a variable

This example illustrates a simple assignment of string data to a variable using the = operator.

DECLARE @dbname VARCHAR(100);

SET @dbname = 'Adventure';

D. String comparison with spaces

The following queries illustrate the comparison between strings where one side contains spaces and the other doesn't:

CREATE TABLE #tmp (c1 VARCHAR(10));
GO

INSERT INTO #tmp VALUES ('abc ');

INSERT INTO #tmp VALUES ('abc');
GO

SELECT DATALENGTH(c1) AS 'EqualWithSpace', * FROM #tmp
WHERE c1 = 'abc ';

SELECT DATALENGTH(c1) AS 'EqualNoSpace  ', * FROM #tmp
WHERE c1 = 'abc';

SELECT DATALENGTH(c1) AS 'GTWithSpace   ', * FROM #tmp
WHERE c1 > 'ab ';

SELECT DATALENGTH(c1) AS 'GTNoSpace     ', * FROM #tmp
WHERE c1 > 'ab';

SELECT DATALENGTH(c1) AS 'LTWithSpace   ', * FROM #tmp
WHERE c1 < 'abd ';

SELECT DATALENGTH(c1) AS 'LTNoSpace     ', * FROM #tmp
WHERE c1 < 'abd';

SELECT DATALENGTH(c1) AS 'LikeWithSpace ', * FROM #tmp
WHERE c1 LIKE 'abc %';

SELECT DATALENGTH(c1) AS 'LikeNoSpace   ', * FROM #tmp
WHERE c1 LIKE 'abc%';
GO

DROP TABLE #tmp;
GO

Next steps