Hi,
I have a column with the datatype "text" that may contain leading whitespace
(tabs, spaces newlines, ...) and I would like to remove them all (ideally
leading and trailing).
I tried
SELECT regexp_replace(myfield, '\A\s*', '')
FROM mytable;
(for leading whitespace, to start with)
But it does not remove anything. I replace my first attempt '^\s*' with '\A\s*'
after reading the chapter about newline-sensitive matching, but that doesn't
seem to do the trick either.
Just for a test I changed this to
SELECT regexp_replace(myfield, '\s*', '')
FROM mytable;
and expected *all* whitespace to be removed from my string, but only the leading
ones were replaced. Which I don't understand at all. Why weren't other
whitespace sequences not replaced with that expression?
What would be the correct RE to replace leading and trailing whitespace without
affecting anything inbetween?
I'm pretty sure I'm missing someting very obvious...
Thanks in advance
Thomas