Substring expression fails on single character input

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Substring expression fails on single character input

Per-Åke Ling
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|

Regards,
Per-Åke Ling
Reply | Threaded
Open this post in threaded view
|

Re: Substring expression fails on single character input

Tom Lane-2
=?UTF-8?Q?Per=2D=C3=85ke_Ling?= <[hidden email]> 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