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?

score 12 · Accepted Answer · answered Jan 20 '12 at 07:36

12

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)

answered Jan 20 '12 at 07:36

Bohemian

412,405
93
575
722

OMG! every time I search the manual I ended up reading [LTRIM()](http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_ltrim) – Shiplu Mokaddim Jan 20 '12 at 07:48

score 1 · Answer 2 · answered Jan 20 '12 at 07:37

1

You'd probably want to use TRIM, as in;

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

answered Jan 20 '12 at 07:37

Joachim Isaksson

176,943
25
281
294

score 0 · Answer 3 · answered Feb 11 '21 at 14:53

0

Very useful. I dump queries into Excel frequently using SQLyog. I ran into this issue exporting a mediumtext field and the trim/trailing function as used here made my exports functional.

answered Feb 11 '21 at 14:53

RLytle

121
1
5

How can I remove padded NULL bytes using SELECT in MySQL

3 Answers3

Linked