The STRCMP Function

stringMySQL has an interesting function name STRCMP mimicking the C++ function having the same name. This function compares two values, returning 0 if the strings are the same, -1 if the first argument is smaller than the second, and 1 otherwise.

MySQL> SELECT c1, c2, strcmp(c1, c2) FROM t1;
+---------+---------+----------------+
| c1      | c2      | strcmp(c1, c2) |
+---------+---------+----------------+
| text    | text    |              0 |
| text123 | text    |              1 |
| text    | text123 |             -1 |
+---------+---------+----------------+
3 rows in set (0.00 sec)

Vertica doesn’t have this function built-in, but it’s very easy to make our own.

dbadmin=> CREATE FUNCTION strcmp(p1 VARCHAR, p2 VARCHAR) RETURN INT
dbadmin-> AS
dbadmin-> BEGIN
dbadmin->   RETURN SIGN(LENGTH(p1) - LENGTH(p2));
dbadmin-> END;
CREATE FUNCTION
dbadmin=> SELECT c1, c2, strcmp(c1, c2) FROM t1;
c1      |   c2    | strcmp
--------+---------+--------
text    | text123 |     -1
text    | text    |      0
text123 | text    |      1
(3 rows)

Have Fun!

Twitter Digg Delicious Stumbleupon Technorati Facebook Email

No comments yet... Be the first to leave a reply!

Leave a Reply

You must be logged in to post a comment.