Extract First Numeric Part Of Field

I have a database (Postgres 7.4) field for address Example Data address | zip -----------------------+-------------+ 123 main street | 12345 -----------

Solution 1:

SELECTsubstring(address, '^\\d+') AS heading_number
FROM   tbl
WHERE  zip =12345AND    address ILIKE '3%'

Returns 1 or more digits from the start of the string. Leave out the anchor ^ if you want the first sequence of digits in the string instead of the sequence at the start. Example:

SELECTsubstring('South 13rd street 3452435 foo', '\\d+');

Read about substring() and regular expressions in the manual. In more recent versions (8.0+, with standard_conforming_strings = on), use escape string syntax like this:

SELECTsubstring('South 13rd street 3452435 foo', E'\\d+');

Or just:

SELECTsubstring('South 13rd street 3452435 foo', '\d+');