MySQL Stored Procedure Programming

String Functions

String functions perform operations on string data types such as VARCHAR, CHAR, and TEXT.

ASCII

    string1=ASCII(string2)

ASCII returns the ASCII character code corresponding to the first character in the provided input string.

Since the ASCII function returns only the ASCII code for the first character, we can create a stored function to extend this capability to allow us to return the ASCII codes corresponding to all of the characters in the string. Example 9-1 shows an implementation of such a stored function. It uses the LENGTH and SUBSTR functions to extract each character in the input string, and then uses the ASCII and CONCAT functions to build up a string consisting of all of the ASCII codes corresponding to the entire input string.

Example 9-1. Using the ASCII function

CREATE FUNCTION ascii_string (in_string VARCHAR(80) )
 RETURNS VARCHAR(256)
 DETERMINISTIC
BEGIN
   DECLARE i INT DEFAULT 1;
      DECLARE string_len INT;
      DECLARE out_string VARCHAR(256) DEFAULT '';

      SET string_len=LENGTH(in_string);
      WHILE (i<string_len) DO
         SET out_string=CONCAT(out_string,ASCII(SUBSTR(in_string,i,1)),' ');
         SET i=i+1;
      END WHILE;
      RETURN (out_string);

END
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
SELECT ascii_string('MySQL Rocks!')
--------------

+---------------------------------------+
| ascii_string('MySQL Rocks!')          |
+---------------------------------------+
| 77 121 83 81 76 32 82 111 99 107 115  |
+---------------------------------------+
1 row in set (0.00 sec)

CHAR ...