Thread: SELECT using RegEx inside a POSITION function

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

"Danny Stewart" <dstewart@pcfa.org> writes:
> 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'

I think you are mixing up substring() and position().  If I understand
your problem correctly, what you need is just the regex-style substring
function:

regression=# select substring('Folder 567 Section 6' from '[0-9]+');
 substring
-----------
 567
(1 row)

            regards, tom lane