有没有办法在MySQL中修剪几个字符(不考虑它们的组合)? [英] Is there a way to trim several characters (disregarding their combination) in MySQL?

问题描述

我想从MySQL的字符串中删除前导和尾随空格(SP，\ n，\ t，最后是\ r).数据已经在MySQL表上，我无法检索它们以在PHP中进行处理，因为这太慢了.

I want to remove leading and trailing spaces ( SP, \n, \t, eventually \r ) from strings in MySQL. Data already are on MySQL tables and I can't retrieve them for processing in PHP as this should be too slow.

我尝试了这种语法:

UPDATE table set field = TRIM(BOTH '\t' FROM TRIM(BOTH '\n' FROM TRIM(field)));

但是，这种方式删除了spaces, then \ n , then \ t in this order, and I want to remove all spaces disregarding their order (ie:"\ n \ t \ t \ n \ n \ t hello \ t \ n \ n \ t" would return only"hello" `.

But, this way removes spaces, then\n, then\tin this order, and I want to remove all spaces disregarding their order (ie:"\n\t \t\n\n\t hello\t\n\n \t "would return only"hello"`.

我想我需要创建一个函数(CREATE FUNCTION MY_TRIM ...)，但是在执行此工作之前，我想知道是否有更简单的方法.

I guess I need to create a function (CREATE FUNCTION MY_TRIM...), but before doing such job, I would like to know if there are easier ways.

推荐答案

您可以尝试以下操作:

    UPDATE table SET `field`=TRIM(BOTH '\t' OR '\n' FROM TRIM(BOTH '\n' OR '\t' FROM TRIM(field))); 

这篇关于有没有办法在MySQL中修剪几个字符(不考虑它们的组合)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！