converting ascii code to character and storing it inside a column of a table

Question

lets say i have a "varchar" variable which contains "ascii" code separating each other by a ',' and i want to convert it to character and insert it into a column. Is there any way that i can do this? I am new in mysql so i was wondering if someone can help.

Example: lets say we are inside a trigger or procedure.

declare test varchar(10);
set test = "73,116";

now i want to convert it into "it" and store it in a column of a table which is varchar as well. Help me with this please.

Answer 1

Iteratively parsing a string is not an easy task for a set-based language such as SQL.

One option uses a recursive query (available in MySQL 8.0):

set @test = '73,116';

with recursive cte as (
    select 
        1 n, 
        0 + substring(@test, 1, locate(',', @test) - 1) part,
        concat(substring(@test, locate(',', @test) + 1), ',') rest
    union all
    select
        n + 1,
        substring(rest, 1, locate(',', rest) - 1),
        substring(rest, locate(',', rest) + 1)
    from cte
    where locate(',', rest) > 0
)
select group_concat(char(part using utf8) order by n separator '') res
from cte;

The recursive query extracts each csv part sequentially, while keeping track of the position. Then, the outer query converts each ASCII code the corresponding character, and re-aggregates the results into a string.

Demo on DB Fiddle:

| res |
| --- |
| It  |