Allows you to directly compare two character strings based on alternate collation rules.
compare ({char_expression1|uchar_expression1},
{char_expression2|uchar_expression2}),
[{collation_name | collation_ID}]
are the character expressions to compare to char_expression2 or uchar_expression 2.
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
can be a quoted string or a character variable that specifies the collation to use. Table 2-4 shows the valid values.
is an integer constant or a variable that specifies the collation to use. Table 2-4 shows the valid values.
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)
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)
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:
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-4 lists the valid values for collation_name and collation_ID.
ANSI SQL – Compliance level: Transact-SQL extension.
Any user can execute compare.
Function sortkey
| Copyright © 2005. Sybase Inc. All rights reserved. |
|
|
