How can I remove filled NULL bytes using SELECT in MySQL

Question

If the data is inserted this way,

insert into `t` (`date`, `data`) values ( now(), lpad('Hello', 4096, CHAR(0x00)));

How do you extract it by removing NULL characters from the column data. I'm really looking for something that does the opposite of what it does LPAD.

Table definition:

create table `t` (
    `id` int auto_increment,
    `date` datetime,
    `data` blob,
    primary key (`id`)
);

General question: how to remove a specific character from the beginning or end of a line?

Answer 1

Use trim () , which comes in 3 flavors:

select trim(leading CHAR(0x00) from data)

or

select trim(trailing CHAR(0x00) from data)

or

select trim(both CHAR(0x00) from data)

Answer 2

You will probably want to use TRIM, as in;

select id, date, trim(trailing char(0) from data) as data from t;