compare

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 or collation_ID – collation_name can be a quoted string or a character variable that specifies the collation to use, while collation_ID is an integer constant or a variable that specifies the collation to use. The valid values are:

  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

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) 

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute compare.