Usage for compare

There are additional considerations for using compare.

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 oruchar_expression2.
- 0 – indicates that char_expression1 or uchar_expression1 is equal to char_expression2 oruchar_expression2.
- -1 – indicates that char_expression1 or uchar_expression1 is less than char_expression2or uchar expression2.
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.

Valid Values for collation_name and collation_ID
Description	Collation Name	Collation ID
Default 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