3

I have a table where message is a varchar column type.

mysql> select * from todel;
+---------+
| message |
+---------+
| 73, 116 |
+---------+
1 row in set (0.00 sec)

I am not able to convert the ASCII values to string.

mysql> select char(message) from todel;
+---------------+
| char(message) |
+---------------+
| I             |
+---------------+
1 row in set, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: '73, 116' |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)

If I use char function directly, then it works as expected:

mysql> select char(73, 116);
+---------------+
| char(73, 116) |
+---------------+
| It            |
+---------------+
1 row in set (0.00 sec)

How do I use the "char" function while selecting varchar data from a table?

shantanuo
  • 31,689
  • 78
  • 245
  • 403
  • 4
    The select version tries to do char('73, 116'). – jarlh Feb 19 '15 at 11:26
  • 1
    I think you need to split it and cast to int somehow. Perhaps this can help http://stackoverflow.com/questions/11835155/mysql-split-comma-separated-string-into-temp-table – Jørgen R Feb 19 '15 at 11:29

1 Answers1

1

You can use SUBSTRING_INDEX() to separate the two strings and then convert them with CHAR()

SELECT CHAR(SUBSTRING_INDEX('73, 116', ',', 1)) --I
SELECT CHAR(SUBSTRING_INDEX('73, 116', ',', -1)) --t
Phate01
  • 2,499
  • 2
  • 30
  • 55