-2

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
Rashid
  • 1
  • 3
  • can you exlain what "73,116"; should result in vbecasu that is no ascii i konow of – nbk Jun 13 '20 at 21:52
  • Hello nbk, thank you for the respond. "73,116" should result "It" as 73= "I' and 116 = "t" . – Rashid Jun 13 '20 at 21:55
  • comma seperated columns are not good for mysqlespecially when the length is not detemined. take alook at https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad it is better to normalize it – nbk Jun 13 '20 at 22:05

1 Answers1

0

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  |
GMB
  • 216,147
  • 25
  • 84
  • 135