Outdated Version

You are viewing an older version of this section. View current production version.

STRING_BYTES

Converts a string to a SingleStore (MemSQL) Procedural SQL (MPSQL) array of integers (TINYINT UNSIGNED) where each array element is the integer byte value of a character, or a byte within a multiple byte character, in the input string. Multiple byte characters in the input string generate more than one entry in the output array.

Syntax

STRING_BYTES(str)

Arguments

  • str: any string

Return Type

An MPSQL array.

Remarks

  • If str is in ASCII format, then the length of the MPSQL array is same as the length of the input string.
  • For UTF-8 characters that require multiple bytes, the MPSQL array will contain the integer value of each byte.

Example

The following example demonstrates the usage of the STRING_BYTES() function through a stored procedure.

USE trades;

DELIMITER //

CREATE OR REPLACE PROCEDURE Procedure1(s VARCHAR(80)) AS
DECLARE
  a ARRAY(TINYINT UNSIGNED NOT NULL);
  l INT;
  BEGIN
  a = STRING_BYTES(s);
  l = LENGTH(a);
  ECHO SELECT l as l;
  FOR i IN a LOOP
    ECHO SELECT i AS i;
  END LOOP;
END //
DELIMITER ;
CALL Procedure1('a€bc');
****

+------+
| l    |
+------+
| 6    |
+------+
1 row in set (0.00 sec)

+----+
| i  |
+----+
| 97 |
+----+
1 row in set (0.00 sec)

+-----+
| i   |
+-----+
| 226 |
+-----+
1 row in set (0.00 sec)

+-----+
| i   |
+-----+
| 130 |
+-----+
1 row in set (0.00 sec)

+-----+
| i   |
+-----+
| 172 |
+-----+
1 row in set (0.00 sec)

+----+
| i  |
+----+
| 98 |
+----+
1 row in set (0.00 sec)

+----+
| i  |
+----+
| 99 |
+----+
1 row in set (0.00 sec)

In the output, the values 97, 98, and 99 are one-byte decimal integer codes for a, b, and c, respectively. The values 226, 130, and 172 represent the three bytes code for the symbol .