I'm trying to retrieve just the numeric portion of the field named 'id' from
a table named 'resources'. The data looks something like:
DEF 345
#234
Folder 567 Section 6
123
NDD #456
ABC (no numbers)
Based on the information contained in
http://techdocs.postgresql.org/guides/RegularExpressionIntro (which does not
mention the position function), I am trying to use
SELECT substring(id from position(~ '[0-9]' in id)) FROM resources ;
but that returns:
Unable to identify a prefix operator '~' for type 'unknown'
You may need to add parentheses or an explicit cast
Is regex not supported inside the position() function? Or, am I missing
something?
Eventually, I want to be able to sort so the records appear in the order
that contains these numbers:
blank or null
123
234
345
456
567
using a statement similar to:
SELECT * FROM resources WHERE somefield='limitingphrase' ORDER BY
whateverthecorrectsubstringstatement
