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

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

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:

Logical Page Size	Maximum row size for DOL table	Maximum row size for APL table
2K (2048 bytes)	1962	1964
4K (4096 bytes	4010	4012
8K (8096 bytes	8096	8108
16K (16384 bytes)	16298	16300

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-3 lists the valid values for collation_name and collation_ID.

**Table 2-3: 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

SQL92 – Compliance level: Transact-SQL extension.

Permissions

Any user can execute compare.