mysql char displaying just the first character

Question

I have this value in my MYSQL table "&#84&#101&#115&#116 " (=="Test") this text is basically in decimal format. more info: https://en.wikipedia.org/wiki/T#Computing_codes i am trying to run a select query as its a regular text. these are the tests i'm running:

1. query: select TRIM(Leading ',' FROM replace(trim(name),"&#",",")) from table where id = 1;

output: 84,101,115,116

2. query: select char(84,101,115,116);

output: Test

3. query: select char(TRIM(Leading ',' FROM replace(trim(name),"&#",","))) from table where id = 1;

output: T

How can i get the whole word instead of just the first character?

Thanks,

Nir.

score 0 · Answer 1 · edited May 23 '17 at 11:51

you are not getting expected result because

TRIM(Leading ',' FROM replace(trim(name),"&#",","))

returns string instead of array

'84,101,115,116'

Then char() only interprets first readable set of numbers.

If you want to continue, take a look at similar question made with procedures.

You should create a procedure that would return string decimals as a table result set and then use it as input to your CHAR(). This way you would move complicated logic to procedure and then use it like this (untested):

select
    GROUP_CONCAT(
        SELECT
            char(*)
        FROM
            (
                CALL database.getDecimalText()
            )
    ) SEPARATOR '' )
from
    table
    where
        id = 1;

score 0 · Answer 2 · answered Sep 22 '16 at 09:46

As stated by Mulcek, your passing what looks like comma separated arguments to the CHAR function, but this is in fact a single string that contains commas. MySQL interprets it as such and returns the decoded first number it recognises.

A one step solution would be to use a prepared statement to build the whole query as a string and then execute that as follows:

SET @VTestVar = '&#84&#101&#115&#116';
SET @VExec = CONCAT('SELECT CHAR(', (SELECT TRIM(Leading ',' FROM REPLACE(TRIM(@VTestVar),"&#",","))), ') value;');

PREPARE stmt FROM @VExec;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Here it is adjusted for your use case:

SET @VTestVar = '&#84&#101&#115&#116';
SET @VExec = CONCAT('SELECT CHAR(', (SELECT TRIM(Leading ',' FROM REPLACE(TRIM(select TRIM(Leading ',' FROM replace(trim(name),"&#",",")) from table where id = 1),"&#",","))), ') value;');

PREPARE stmt FROM @VExec;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Regards,

James

mysql char displaying just the first character

2 Answers2