How can I remove padded NULL bytes using SELECT in MySQL

Question

If data is inserted this way,

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

How do you retrive it removing NULL characters from data column. I am infact looking for something that does the reverse of what LPAD does.

The table definition is,

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

Generalized question is, How can I remove an specific character from either beginning or ending of a string?

Answer 1

Use trim(), which comes in 3 flavours:

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'd probably want to use TRIM, as in;

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