Thread: Substring expression fails on single character input
When using the expression '(\S.*\S)' to remove surrounding whitespace (including \t and \n) it returns NULL on single character surrounded by whitespace.
The following shows the unexpected result:
SELECT x, '|'||substring(x, '(\S.*\S)')||'|'
FROM (VALUES (' a'), (' ab'), (' a b c '), (E' c\n'), (E' ab\n')) AS z (x);
x │ ?column?
──────────┼──────────
a │ <=== ERROR
ab │ |ab|
a b c │ |a b c|
c ↵│ <=== ERROR
│
ab ↵│ |ab|
──────────┼──────────
a │ <=== ERROR
ab │ |ab|
a b c │ |a b c|
c ↵│ <=== ERROR
│
ab ↵│ |ab|
Regards,
Per-Åke Ling
=?UTF-8?Q?Per=2D=C3=85ke_Ling?= <perake.ling@gmail.com> writes: > When using the expression *'(\S.*\S)' *to remove surrounding whitespace > (including \t and \n) it returns NULL on single character surrounded by > whitespace. I see no bug there. The pattern requires two non-white-space characters surrounding some arbitrary text, and your examples don't have that. If your goal is to trim leading/trailing whitespace I'd suggest that btrim is a lot easier route to the goal than coming up with a correct regex. regards, tom lane