Umesh Chandra

For functions that operate on string positions, the first position is numbered 1.

ASCII(str)

Returns the numeric value of the leftmost character of the string str.

Returns 0 if str is the empty string.

Returns NULL if str is NULL. 

ASCII( ) works for 8-bit characters.

Code:

mysql> SELECT ASCII('2');
        -> 50
mysql> SELECT ASCII(2);
        -> 50
mysql> SELECT ASCII('dx');
        -> 100

---

BIT_LENGTH(str)

Returns the length of the string str in bits.

Returns NULL if str is NULL.

Code:

mysql> SELECT BIT_LENGTH('text');
        -> 32

---

INSERT(str, pos, len, newstr)

Returns the string str, with the substring beginning at position pos and len characters long replaced by the string newstr.

Returns the original string if pos is not within the length of the string.

Replaces the rest of the string from position pos if len is not within the length of the rest of the string.

Returns NULL if any argument is NULL.

Code:

mysql> SELECT INSERT('Quadratic', 3, 4, 'What');
        -> 'QuWhattic'
mysql> SELECT INSERT('Quadratic', -1, 4, 'What');
        -> 'Quadratic'
mysql> SELECT INSERT('Quadratic', 3, 100, 'What');
        -> 'QuWhat'

---

INSTR(str, substr)

Returns the position of the first occurrence of substring substr in string str.

Code:

mysql> SELECT INSTR('foobarbar', 'bar');
        -> 4
mysql> SELECT INSTR('xbar', 'foobar');
        -> 0

---

LENGTH(str)

Returns the length of the string str, measured in bytes.

A multibyte character counts as multiple bytes.

This means that for a string containing five 2-byte characters,  LENGTH() returns 10

Code:

mysql> SELECT LENGTH('text');
        -> 4

---

CHAR_LENGTH(str)

Returns the length of the string str, measured in code points.

A multibyte character counts as a single code point.

This means that, for a string containing two 3-byte characters, LENGTH() returns 6, whereas CHAR_LENGTH() returns 2, as shown here:

CHAR_LENGTH() returns NULL if str is NULL.

CHARACTER_LENGTH() is a synonym for CHAR_LENGTH().

Code:

mysql> SET @dolphin:='海豚';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT LENGTH(@dolphin), CHAR_LENGTH(@dolphin);
+------------------+-----------------------+
| LENGTH(@dolphin) | CHAR_LENGTH(@dolphin) |
+------------------+-----------------------+
|                6 |                     2 |
+------------------+-----------------------+
1 row in set (0.00 sec)

---

CONCAT(str1, str2, . . .)

Returns the string that results from concatenating the arguments. May have one or more arguments.

If all arguments are nonbinary strings, the result is a nonbinary string.

If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent nonbinary string form.

CONCAT() returns NULL if any argument is NULL.

Code:

mysql> SELECT CONCAT('My', 'S', 'QL');
        -> 'MySQL'
mysql> SELECT CONCAT('My', NULL, 'QL');
        -> NULL
mysql> SELECT CONCAT(14.3);
        -> '14.3'

---

CONCAT_WS(seperator, str1, str2, . . .)

CONCAT_WS stands for Concatenate With Separator and is a special form of CONCAT().

The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments.

If the separator is NULL, the result is NULL.

CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.

Code:

mysql> SELECT CONCAT_WS(',','First name','Second name','Last Name');
        -> 'First name,Second name,Last Name'
mysql> SELECT CONCAT_WS(',','First name',NULL,'Last Name');
        -> 'First name,Last Name'

---

REPEAT(str, count)

Returns a string consisting of the string str repeated count times.

If count is less than 1, returns an empty string.

Returns NULL if str or count is NULL.

Code:

mysql> SELECT REPEAT('MySQL', 3);
        -> 'MySQLMySQLMySQL'

---

REPLACE(str, from_str, to_str)

Returns the string str with all occurrences of the string from_str replaced by the string to_str. REPLACE() performs a case-sensitive match when searching for from_str.

This function is multibyte safe. It returns NULL if any of its arguments are NULL.

Code:

mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
        -> 'WwWwWw.mysql.com'

---

REVERSE(str)

Returns the string str with the order of the characters reversed, or NULL if str is NULL.

Code:

mysql> SELECT REVERSE('abc');
        -> 'cba'