compare

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

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

• 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 six 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. Adaptive Server issues a warning message, but the query or transaction that contained the compare function continues to run. 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-5: 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

• Both char_expression1, uchar_expression1, and char_expression2, uchar_expression2 must be characters that are encoded in the server’s default character set.

• 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 0.

  • If char_expression1 or uchar_expression 1 is empty, the function returns -1.

  The compare function does not equate empty strings and strings containing only spaces. 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 do not compare equally.

• If either char_expression1, uchar_expression1; or char_expression2, uchar_expression2 is NULL, then the result is 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-6 lists the valid values for collation_name and collation_ID.

  Table 2-6: Collation names and IDs

  Description	Collation name	Collation ID
  Deafult Unicode multilingual	default	20
  Thai dictionary order	thaidict	21
  ISO14651 standard	iso14651	22
  UTF-16 ordering – matches UTF-8 binary ordering	utf8bin	24
  CP 850 Alternative – no accent	altnoacc	39
  CP 850 Alternative – lowercase first	altdict	45
  CP 850 Western European – no case preference	altnocsp	46
  CP 850 Scandinavian – dictionary ordering	scandict	47
  CP 850 Scandinavian – case-insensitive with preference	scannocp	48
  GB Pinyin	gbpinyin	n/a
  Binary sort	binary	50
  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 Russian dictionary	rusdict	58
  ISO 8859-5 Russian no case	rusnocs	59
  ISO 8859-5 Cyrillic dictionary	cyrdict	63
  ISO 8859-5 Cyrillic no case	cyrnocs	64
  ISO 8859-7 Greek dictionary	elldict	65
  ISO 8859-2 Hungarian dictionary	hundict	69
  ISO 8859-2 Hungarian no accents	hunnoac	70
  ISO 8859-2 Hungarian no case	hunnocs	71
  ISO 8859-9 Turkish dictionary	turdict	72
  ISO 8859-9 Turkish no accents	turknoac	73
  ISO 8859-9 Turkish no case	turknocs	74
  CP932 binary ordering	cp932bin	129
  Chinese phonetic ordering	dynix	130
  GB2312 binary ordering	gb2312bn	137
  Common Cyrillic dictionary	cyrdict	140
  Turkish dictionary	turdict	155
  EUCKSC binary ordering	euckscbn	161
  Chinese phonetic ordering	gbpinyin	163
  Russian dictionary ordering	rusdict	165
  SJIS binary ordering	sjisbin	179
  EUCJIS binary ordering	eucjisbn	192
  BIG5 binary ordering	big5bin	194
  Shift-JIS binary order	sjisbin	259

Function sortkey

---

Copyright © 2009. Sybase Inc. All rights reserved.

View this document as PDF