BPCHARCMP function
Compares the value of two strings and returns an integer. If the strings are
identical, the function returns 0
. If the first string is greater alphabetically, the function returns 1
. If the
second string is greater, the function returns -1
.
For multibyte characters, the comparison is based on the byte encoding.
Synonym of BTTEXT_PATTERN_CMP function.
Syntax
BPCHARCMP(string1, string2)
Arguments
- string1
-
A
CHAR
string or aVARCHAR
string. - string2
-
A
CHAR
string or aVARCHAR
string.
Return type
INTEGER
Examples
The following examples use the USERS table from the TICKIT sample database. For more information, see Sample database.
To determine whether a user's first name is alphabetically
greater than the user's last name for the first ten entries in the USERS table, use the following example. For entries where the string for FIRSTNAME is later
alphabetically than the string for LASTNAME, the function returns 1
. If the LASTNAME is
alphabetically later than FIRSTNAME, the function returns -1
.
SELECT userid, firstname, lastname, BPCHARCMP(firstname, lastname) FROM users ORDER BY 1, 2, 3, 4 LIMIT 10;
+--------+-----------+-----------+-----------+ | userid | firstname | lastname | bpcharcmp | +--------+-----------+-----------+-----------+ | 1 | Rafael | Taylor | -1 | | 2 | Vladimir | Humphrey | 1 | | 3 | Lars | Ratliff | -1 | | 4 | Barry | Roy | -1 | | 5 | Reagan | Hodge | 1 | | 6 | Victor | Hernandez | 1 | | 7 | Tamekah | Juarez | 1 | | 8 | Colton | Roy | -1 | | 9 | Mufutau | Watkins | -1 | | 10 | Naida | Calderon | 1 | +--------+-----------+-----------+-----------+
To return all entries in the USERS table where the function returns 0
, use the following example. The function returns 0
when FIRSTNAME is identical to LASTNAME.
SELECT userid, firstname, lastname, BPCHARCMP(firstname, lastname) FROM users WHERE BPCHARCMP(firstname, lastname)=0 ORDER BY 1, 2, 3, 4;
+--------+-----------+----------+-----------+ | userid | firstname | lastname | bpcharcmp | +--------+-----------+----------+-----------+ | 62 | Chase | Chase | 0 | | 4008 | Whitney | Whitney | 0 | | 12516 | Graham | Graham | 0 | | 13570 | Harper | Harper | 0 | | 16712 | Cooper | Cooper | 0 | | 18359 | Chase | Chase | 0 | | 27530 | Bradley | Bradley | 0 | | 31204 | Harding | Harding | 0 | +--------+-----------+----------+-----------+