compare

Description

Allows you to directly compare two character strings based on alternate collation rules.

Syntax

compare ({char_expression1|uchar_expression1}, 
	{char_expression2|uchar_expression2}), 
	[{collation_name | collation_ID}] 

Parameters

char_expression1 or uchar_expression1

are the character expressions to compare to char_expression2 or uchar_expression 2.

char_expression2 or uchar_expression2

are the character expressions against which to compare char_expression1 or uchar_expression1.

char_expression1 and char_expression2 can be:

  • Character type (char, varchar, nchar, or nvarchar)

  • Character variable, or

  • Constant character expression, enclosed in single or double quotation marks

uchar_expression1 and uchar_expression2 can be:

  • Character type (unichar or univarchar)

  • Character variable, or

  • Constant character expression, enclosed in single or double quotation marks

collation_name

can be a quoted string or a character variable that specifies the collation to use. Table 2-6 shows the valid values.

collation_ID

is an integer constant or a variable that specifies the collation to use. Table 2-6 shows the valid values.

Examples

Example 1

Compares aaa and bbb:

1> select compare ("aaa","bbb") 
2> go 
 ----------- 
          -1 
(1 row affected) 

Alternatively, you can also compare aaa and bbb using this format:

1> select compare (("aaa"),("bbb")) 
2> go 
 ----------- 
          -1 
(1 row affected)

Example 2

Compares aaa and bbb and specifies binary sort order:

1> select compare ("aaa","bbb","binary") 
2> go 
 ----------- 
          -1 
(1 row affected)

Alternatively, you can compare aaa and bbb using this format, and the collation ID instead of the collation name:

1> select compare (("aaa"),("bbb"),(50)) 
2> go 
 ----------- 
          -1 
(1 row affected) 

Usage

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute compare.

See also

Function sortkey