The documentation says that \s is whitespace and \S is not whitespace. So far, nothing new to regex users.
But let's check some return values:
SELECT SUBSTRING('abc a c' FROM 'a\\sc');
'a c'
SELECT SUBSTRING('abc a c' FROM 'a[\\s]c'); -- Note the character class
'a c'
SELECT SUBSTRING('abc a c' FROM 'a\\Sc');
'abc'
SELECT SUBSTRING('abc a c' FROM 'a[\\S]c'); -- Note the character class
ERROR: invalid regular expression: invalid escape \ sequence
So it seems, \s can be used in a character class and \S cannot. Why?
The \s metacharacter matches whitespace character. Whitespace characters can be: A space character.
Character Classes (a.k.a. Special Sequences)\w | Matches alphanumeric characters, which means a-z , A-Z , and 0-9 . It also matches the underscore, _ . \d | Matches digits, which means 0-9 . \D | Matches any non-digits.
PostgreSQL employs Regular Expressions to get around pattern matching. In this article, we will learn about PostgreSQL Regex. PostgreSQL uses POSIX or “Portable Operating System Interface for Unix” regular expressions, which are better than LIKE and SIMILAR TO operators used for pattern matching.
\s : matches any whitespace. This includes tabs, newlines, form feeds, and any character in the Unicode Z Category (which includes a variety of space characters and other separators.). The complement, \S , matches any non-whitespace character.
From the manual:
Within bracket expressions, \d, \s, and \w lose their outer brackets, and \D, \S, and \W are illegal.
In any case, the brackets seem redundant since \s
and \S
themselves are character classes.
The following syntax works for me as an alternative to a[\\S]c
:
SELECT SUBSTRING('abc a c' FROM 'a[^[:space:]]c');
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