Menu
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.
737
The TRIM() function removes leading and trailing spaces from a string.
Use the TRIM() function with the LEADING keyword to remove characters at the beginning of a string. TRIM() allows you to remove specific character(s) or space(s) from the beginning, end, or both ends of a string. This function takes the following arguments: An optional keyword that specifies the end(s) to trim.
How do I trim a space in MySQL? LTRIM() is used to remove the leading spaces (spaces on the left side) from a string. RTRIM() is used to remove the trailing spaces (spaces on the right side) from a string. TRIM() is used to remove the leading and the trailing spaces from a string.
The TRIM() function returns a string that has unwanted characters removed. Note that to remove the leading spaces from a string, you use the LTRIM() function. And to remove trailing spaces from a string, you use the RTRIM() function.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
