Home / Funtions and Operators / Question No: 234

String functions calculate string lengths, extract pieces of strings, search for substrings or replace them, perform lettercase conversion, and more.

LENGTH() and CHAR_LENGTH() functions

The LENGTH() and CHAR_LENGTH() functions determine string lengths in byte and character units, respectively. The values returned by the two functions will differ for strings that contain multi-byte characters. The following example shows this, using the latin1 single-byte character set and the ucs2 double-byte character set:

mysql> SET @s = CONVERT('MySQL' USING latin1);
mysql> SELECT LENGTH(@s), CHAR_LENGTH(@s);
+------------+-----------------+
| LENGTH(@s) | CHAR_LENGTH(@s) |
+------------+-----------------+
|          5 |               5 |
+------------+-----------------+


mysql> SET @s = CONVERT('MySQL' USING ucs2);
mysql> SELECT LENGTH(@s), CHAR_LENGTH(@s);
+------------+-----------------+
| LENGTH(@s) | CHAR_LENGTH(@s) |
+------------+-----------------+
|         10 |               5 |
+------------+-----------------+

CONCAT() and CONCAT_WS()

CONCAT() and CONCAT_WS() concatenate strings. CONCAT() concatenates all of its arguments, whereas CONCAT_WS() interprets its first argument as a separator to place between the following arguments:

mysql> SELECT CONCAT('aa','bb','cc','dd');
+-----------------------------+
| CONCAT('aa','bb','cc','dd') |
+-----------------------------+
| aabbccdd                    |
+-----------------------------+

mysql> SELECT CONCAT_WS('aa','bb','cc','dd');
+--------------------------------+
| CONCAT_WS('aa','bb','cc','dd') |
+--------------------------------+
| bbaaccaadd                     |
+--------------------------------+

The two functions also differ in their handling of NULL values. CONCAT() returns NULL if any of its arguments are null. CONCAT_WS() ignores NULL values:

mysql> SELECT CONCAT('/','a',NULL,'b'), CONCAT_WS('/','a',NULL,'b');
+--------------------------+-----------------------------+
| CONCAT('/','a',NULL,'b') | CONCAT_WS('/','a',NULL,'b') |
+--------------------------+-----------------------------+
| NULL                     | a/b                         |
+--------------------------+-----------------------------+

String Compare Funtion

The STRCMP() function compares two strings and returns –1, 0, or 1 if the first string is less than, equal to, or greater than the second string, respectively:

mysql> SELECT STRCMP('abc','def'), STRCMP('def','def'), STRCMP('def','abc');
+---------------------+---------------------+---------------------+
| STRCMP('abc','def') | STRCMP('def','def') | STRCMP('def','abc') |
+---------------------+---------------------+---------------------+
|                  -1 |                   0 |                   1 |
+---------------------+---------------------+---------------------+

Encryption Functions

MySQL encrypts passwords in the grant tables using the PASSWORD() function. This function should be considered for use only for managing MySQL accounts, not for general user applications. One reason for this is that applications often require reversible (two-way) encryption, and PASSWORD() performs irreversible (one-way) encryption. Another reason that applications should avoid reliance on PASSWORD() is that its implementation may change. (In fact, it did change in MySQL 4.1.0 and again in 4.1.1.)

For applications that work with data that must not be stored in unencrypted form, MySQL provides several pairs of functions that perform two-way encryption and decryption:

• ENCODE() and DECODE()
• DES_ENCRYPT() and DES_DECRYPT()
• AES_ENCRYPT() and AES_DECRYPT()

Cryptographically, AES_ENCRYPT() and AES_DECRYPT() can be considered the most secure of the pairs. DES_ENCRYPT() and DES_DECRYPT() can be used if SSL support is enabled.