Whitespace in a database field is not removed by trim()

Question

I have some whitespace at the begining of a paragraph in a text field in MySQL.

Using trim($var_text_field) in PHP or TRIM(text_field) in MySQL statements does absolutely nothing. What could this whitespace be and how do I remove it by code?

If I go into the database and backspace it out, it saves properly. It's just not being removed via the trim() functions.

Answer 1

function UberTrim($s) {
    $s = preg_replace('/\xA0/u', ' ', $s);  // strips UTF-8 NBSP: "\xC2\xA0"
    $s = trim($s);
    return $s;
}

The UTF-8 character encoding for a no-break space, Unicode (U+00A0), is the 2-byte sequence C2 A0. I tried to make use of the second parameter to trim() but that didn't do the trick. Example use:

assert("abc" === UberTrim("  \r\n  \xc2\xa0  abc  \t \xc2\xa0   "));

---

A MySQL replacement for TRIM(text_field) that also removes UTF no-break spaces, thanks to @RudolfRein's comment:

TRIM(REPLACE(text_field, '\xc2\xa0', ' '))

---

UTF-8 checklist:

(more checks here)

1. Make sure your PHP source code editor is in UTF-8 mode without BOM. Or set in the preferences.

2. Make sure your MySQL client is set for UTF-8 character encoding (more here and here), e.g.

   $pdo = new PDO('mysql:host=...;dbname=...;charset=utf8',$userid,$password); $pdo->exec("SET CHARACTER SET utf8");

3. Make sure your HTTP server is set for UTF-8, e.g. for Apache:

   AddDefaultCharset UTF-8

4. Make sure the browser expects UTF-8.

   header('Content-Type: text/html; charset=utf-8');

   or

   <meta http-equiv="Content-Type" content="text/html;charset=utf-8" />