MySQL: trim *both* whitespace and newline characters

Question

I've got a text column whose content has a mix of both newline and whitespace characters in the front and rear of the string. I'm trying to write a SELECT statement which shows me the content without the leading and trailing junk.

The following query trims whitespaces:

SELECT TRIM(column)
FROM table;

While this one trims newlines:

SELECT TRIM('\n' FROM column)
FROM table;

I've also tried this answer but it doesn't work:

SELECT TRIM(BOTH '\t' OR '\n' FROM TRIM(BOTH '\n' OR '\t' FROM TRIM(column)))
FROM table;

Is there a way to remove the mix of both types of leading and trailing characters?

UPDATE: I can't REPLACE because I still want the whitespace/newline characters to be present when they occur inside the content.

I have some fields with a whitespace followed by a bunch of newlines which are subsequently followed by more whitespace/newlines. Hence the dilemma.

Answer 1

Unfortunately TRIM(BOTH '\n' OR '\r' FROM myfield) is not working. So following code only trims one newline (\r\n) statement, and not multiple:

SELECT TRIM(TRIM(BOTH '\n' FROM(TRIM(BOTH '\r' FROM myfield)))) FROM mytable

Note that the statement above does not trim <space>\r\n mytext to mytext, because \r\n is not at the beginning.

Solution for MariaDB

If you are using MariaDB (https://mariadb.com/kb/en/mariadb/pcre/) you could use regular expression to solve your problem. Following expression removes all white space prefix from the text:

SELECT REGEXP_REPLACE(myfield, '^[\r\n ]*(.*)', '\\1') FROM mytable