如何删除 MySQL 字段中的前导和尾随空格? [英] How to remove leading and trailing whitespace in a MySQL field?

问题描述

我有一个包含两个字段(国家和 ISO 代码)的表格:

I have a table with two fields (countries and ISO codes):

Table1

   field1 - e.g. 'Afghanistan' (without quotes)
   field2 - e.g. 'AF'(without quotes)

在某些行中，第二个字段的开头和/或结尾有空格，这会影响查询.

In some rows the second field has whitespace at the start and/or end, which is affecting queries.

Table1

   field1 - e.g. 'Afghanistan' (without quotes) 
   field2 - e.g. ' AF' (without quotes but with that space in front)

有没有办法(在 SQL 中)遍历表并查找/替换 field2 中的空格?

Is there a way (in SQL) to go through the table and find/replace the whitespace in field2?

推荐答案

您正在寻找 TRIM.

UPDATE FOO set FIELD2 = TRIM(FIELD2);

---

似乎值得一提的是，TRIM 可以支持多种类型的空格，但一次只能支持一种，并且默认情况下会使用一个空格.但是，您可以嵌套 TRIMs.

 TRIM(BOTH ' ' FROM TRIM(BOTH '\n' FROM column))

如果你真的想在一次调用中去掉所有的空格，你最好使用 REGEXP_REPLACE 和 [[:space:]] 符号.下面是一个例子:

If you really want to get rid of all the whitespace in one call, you're better off using REGEXP_REPLACE along with the [[:space:]] notation. Here is an example:

SELECT 
    -- using concat to show that the whitespace is actually removed.
    CONCAT(
         '+', 
         REGEXP_REPLACE(
             '    ha ppy    ', 
             -- This regexp matches 1 or more spaces at the beginning with ^[[:space:]]+
             -- And 1 or more spaces at the end with [[:space:]]+$
             -- By grouping them with `()` and splitting them with the `|`
             -- we match all of the expected values.
             '(^[[:space:]]+|[[:space:]]+$)', 

             -- Replace the above with nothing
             ''
         ), 
         '+') 
    as my_example;
-- outputs +ha ppy+

这篇关于如何删除 MySQL 字段中的前导和尾随空格?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！