Category: ASE: Functions: String Functions

compare()

The string function compare() compares two character strings . Comparison is done on the basis of the sort order (Collating Sequence) of the character set . This can be passed to the function either with the parameter "collation_name" or "collation_id".

Depending on the sort order, the string function compare() returns different values Werte .

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

Syntax of the string function compare()

    compare ( {char_expression1 | uchar_expression1},

            {char_expression2 | uchar_expression2} ),

            [{collation_name | collation_id}] 
    

The parameters of the string function compare()

"char_expression1" and "char_expression2"
The parameter "char_expression1" is being compared to the parameter "char_expression2". Both parameters must be passed either in single or double quotes. Furthermore they can either be variables or constants and must be of on of these datatypes: char, varchar, nchar or nvarchar .

"uchar_expression1" and "uchar_expression2"
The parameter "uchar_expression1" is compared to the parameter "uchar_expression2" . Both parameters must be passed either in single or double quotes. Furthermore they can be either variables or constants and must be either of the following datatypes: unichar or univarchar .

"collation_name" and "collation_id"
The parameter "collation_name" must be a valid name of a sort order (collating name), in single or double quotes. See table below for "collation names and ids". The "collation_id" can be used alternatively to the collation name. If the parameter "collation_name" or "collation_id" is not set, the function uses a binary sort order by default.

Example for the string function compare()

    select compare("abc", "xyz")

    go

    -----

    -1
    

Returns "-1", because the value "abc" is less than "xyz".

    select compare("abc", "abc")

    go

    -----

    0
    

Returns "0", because the value of "abc" is equal to "abc".

    select compare("xyz", "abc")

    go

    -----

    1
    

Returns "1", because the value of "xyz" is greater than "abc".

    select compare("xyz", "abc", "binary")

    go

    -----

    1
    

Binary sort order. Returns "1", because the value of "xyz" is greater than "abc".

    select compare(("xyz"), ("abc"), (50))

    go

    -----

    1
    

Here, as an alternative to binary sorting, the "collating_id" is passed to compare(). Returns "1", because the value of "xyz" is greater than "abc".

Problems with the string function compare()

When comparing two values, the function compare() can produce up to six bytes of information for each character to be compared. Thus it can happen that the storage limit is reached. In this case the result is returned truncated. If this happens, a warning will be returned, the query or transaction will not be aborted though and continue to execute. The storage limit of the string function depends on the Page Size of ASE®s . If the return value needs to be truncated due to lack of storage space, the return value adapts to the maximum storage capacity of the corresponding DOL and APL tables.

See also:

ASE T-SQL - Aggregate Functions,
ascii(),
char(),
char(n),
charindex(),
char_length(),
difference(),
lower(),
ltrim(),
patindex(),
replicate(),
reverse(),
right(),
rtrim(),
sortkey(),
soundex(),
space(),
str(),
String Functions,
stuff(),
substring(),
to_unichar(),
uhighsurr(),
ulowsurr(),
upper(),
uscalar().