The STRCMP Function

Moderator: NorbertKrupa

Post Reply
User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1806
Joined: Sat Jan 21, 2012 4:58 am
Contact:

The STRCMP Function

Post by JimKnicely » Tue Feb 12, 2013 3:31 pm

MySQL 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.

In MySQL:

Code: Select all

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.

Code: Select all

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!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Tips, Lessons and Examples”