Mysql – How to remove leading and trailing whitespace in a MySQL field

fieldMySQLremoving-whitespace

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)

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

Best Answer

You're looking for TRIM.

UPDATE FOO set FIELD2 = TRIM(FIELD2);

Seems like it might be worth it to mention that TRIM can support multiple types of whitespace, but only one at a time and it will use a space by default. You can, however, nest TRIMs.

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

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+

Related Solutions

Mysql – concatenate multiple MySQL rows into one field

You can use GROUP_CONCAT:

SELECT person_id,
   GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Ludwig stated in his comment, you can add the DISTINCT operator to avoid duplicates:

SELECT person_id,
   GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Jan stated in their comment, you can also sort the values before imploding it using ORDER BY:

SELECT person_id, 
       GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Dag stated in his comment, there is a 1024 byte limit on the result. To solve this, run this query before your query:

SET group_concat_max_len = 2048;

Of course, you can change 2048 according to your needs. To calculate and assign the value:

SET group_concat_max_len = CAST(
                     (SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
                           FROM peoples_hobbies
                           GROUP BY person_id) AS UNSIGNED);

C# – the difference between a field and a property

Properties expose fields. Fields should (almost always) be kept private to a class and accessed via get and set properties. Properties provide a level of abstraction allowing you to change the fields while not affecting the external way they are accessed by the things that use your class.

public class MyClass
{
    // this is a field.  It is private to your class and stores the actual data.
    private string _myField;

    // this is a property. When accessed it uses the underlying field,
    // but only exposes the contract, which will not be affected by the underlying field
    public string MyProperty
    {
        get
        {
            return _myField;
        }
        set
        {
            _myField = value;
        }
    }

    // This is an AutoProperty (C# 3.0 and higher) - which is a shorthand syntax
    // used to generate a private field for you
    public int AnotherProperty { get; set; } 
}

@Kent points out that Properties are not required to encapsulate fields, they could do a calculation on other fields, or serve other purposes.

@GSS points out that you can also do other logic, such as validation, when a property is accessed, another useful feature.

Best Answer

Related Solutions

Mysql – concatenate multiple MySQL rows into one field

C# – the difference between a field and a property

Related Topic