Extract first numeric part of field

Question

I have a database (Postgres 7.4) field for address

Example Data

    address            |    zip
-----------------------+-------------+
123 main street        |    12345
-----------------------+-------------+
3 where road           |    12345
-----------------------+-------------+
South 3 where road     |    12345

The queries

SELECT *
FROM tbl
WHERE zip = 12345
AND address ILIKE '3%'

I get all but I don't want 123 main street

SELECT *
FROM tbl
WHERE zip = 12345
AND address ILIKE '123%'

I get the results I want

My question is how do I just match the numeric part of the address?

Answer 1

Try this:

SELECT substring(address, '^\\d+') AS heading_number
FROM   tbl
WHERE  zip = 12345
AND    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:

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

Read about substring() and regular expressions in the manual.
In more recent versions (8.0+), don't forget to use for escape string syntax like this:

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