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 you want to compare to char_expression2 or uchar_expression 2.

char_expression2 or uchar_expression2

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

char_expression1 and char_expression2 can be one of the following:

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 one of the following:

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-5 shows the valid values.

collation_ID: is an integer constant or a variable that specifies the collation to use. Table 2-5 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 the following 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 also compare aaa and bbb using the following format, and the collation ID instead of the collation name:

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

 ----------- 
          -1 
(1 row affected)

Usage

The compare function returns the following values, based on the collation rules that you chose:
- 1 – indicates that char_expression1 or uchar_expression1 is greater than char_expression2 or uchar_expression2.
- 0 – indicates that char_expression1 or uchar_expression1 is equal to char_expression2 or uchar_expression2.
- -1 – indicates that char_expression1 or uchar_expression1 is less than char_expression2 or uchar expression2.

compare can generate up to 6 bytes of collation information for each input character. Therefore, the result from using compare may exceed the length limit of the varbinary datatype. If this happens, the result is truncated to fit. Since this limit is dependent on the logical page size of your server, truncation removes result bytes for each input character until the result string is less than the following for DOL and APL tables:

**Table 2-4: Maximum row and column length—APL and DOL**
Locking scheme	Page size	Maximum row length	Maximum column length
APL tables	2K (2048 bytes)	1962	1960 bytes
	4K (4096 bytes)	4010	4008 bytes
	8K (8192 bytes)	8106	8104 bytes
	16K (16384 bytes)	16298	16296 bytes
DOL tables	2K (2048 bytes)	1964	1958 bytes
	4K (4096 bytes)	4012	4006 bytes
	8K (8192 bytes)	8108	8102 bytes
	16K (16384 bytes)	16300	16294 bytes if table does not include any variable length columns
	16K (16384 bytes)	16300 (subject to a max start offset of varlen = 8191)	8191-6-2 = 8183 bytes if table includes at least on variable length column.*
* This size includes six bytes for the row overhead and two bytes for the row length field

If this occurs, Adaptive Server issues a warning message, but the query or transaction that contained the compare function continues to run.

Both char_expression1, uchar_expression1, and char_expression2 and uchar_expression2 must be characters that are encoded in the server’s default character set.
Either char_expression1, uchar_expression 1, or char_expression2, uchar_expression2, or both, can be empty strings:
- If char_expression2 or uchar_expression2 is empty, the function returns 1.
- If both strings are empty, then they are equal, and the function returns a 0 value.
- If char_expression1 or uchar_expression 1 is empty, the function returns a -1.
The compare function does not equate empty strings and strings containing only spaces, as does. compare uses the sortkey function to generate collation keys for comparison. Therefore, a truly empty string, a string with one space, or a string with two spaces will not compare equally.
If either char_expression1, uchar_expression1; or char_expression2, uchar_expression2 is NULL, then the result will be NULL.
If a varchar expression is given as one parameter and a unichar expression is given as the other, the varchar expression is implicitly converted to unichar (with possible truncation).
If you do not specify a value for collation_name or collation_ID, compare assumes binary collation.

Table 2-5 lists the valid values for collation_name and collation_ID.

**Table 2-5: Collation names and IDs**
Description	Collation name	Collation ID
Binary sort	binary	50
Default Unicode multilingual	default	0
CP 850 Alternative no accent	altnoacc	39
CP 850 Alternative lower case first	altdict	45
CP 850 Alternative no case preference	altnocsp	46
CP 850 Scandinavian dictionary	scandict	47
CP 850 Scandinavian no case preference	scannocp	48
GB Pinyin	gbpinyin	n/a
Latin-1 English, French, German dictionary	dict	51
Latin-1 English, French, German no case	nocase	52
Latin-1 English, French, German no case preference	nocasep	53
Latin-1 English, French, German no accent	noaccent	54
Latin-1 Spanish dictionary	espdict	55
Latin-1 Spanish no case	espnocs	56
Latin-1 Spanish no accent	espnoac	57
ISO 8859-5 Cyrillic dictionary	cyrdict	63
ISO 8859-5 Russian dictionary	rusdict	58
ISO 8859-9 Turkish dictionary	turdict	72
Shift-JIS binary order	sjisbin	259
Thai dictionary	thaidict	1

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute compare.