如何在MySQL中使用SELECT删除填充的NULL字节 [英] How can I remove padded NULL bytes using SELECT in MySQL

问题描述

如果以此方式插入数据，

If data is inserted this way,

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

如何检索它，从而从data列中删除NULL字符.我实际上是在寻找与LPAD相反的东西.

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.

表的定义是

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?

推荐答案

使用 trim()，共有3种口味:

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

或

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

或

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

这篇关于如何在MySQL中使用SELECT删除填充的NULL字节的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！