O'Reilly logo

MySQL Stored Procedure Programming by Steven Feuerstein, Guy Harrison

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required