Select only numeric part of string only if it starts with a numeric value

Question

This is only giving me the last character (numeric) but I need the whole numeric string

SELECT substring('123 Main Street' FROM '%#"[0-9]#"%' FOR '#')

• Results: 3
• Expecting: 123

This gives me the same results but I need it to return a blank value:

SELECT substring('Main 123 Street' FROM '%#"[0-9]#"%' FOR '#')

• Results: 3
• Expecting:

NOTE: Postgres 7.4

Helpful Link: http://www.postgresql.org/docs/7.4/static/functions-matching.html

UPDATE:

SELECT substring('Main 123 Street' FROM '[0-9]+')
SELECT substring('123 Main Street' FROM '[0-9]+')

• Both now return: 123
• Still need to skip or return full string of: 'Main 123 Street'

UPDATE 2:

Almost have it:

This gives me the results I want if it doesn't start with a numeric value:

SELECT 
    COALESCE(substring('Main 123 Street' FROM '[0-9]*') || 'Main 123 Street', ''),
    substring('Main 123 Street' FROM '[0-9]*')

But this gives me both and I only want the second condition:

SELECT 
    COALESCE(substring('123 Main Street' FROM '[0-9]*') || '123 Main Street', ''),
    substring('123 Main Street' FROM '[0-9]*')

I GOT IT!!! Thanks for all who posted:

SELECT CASE
    WHEN COALESCE(substring(db_column FROM '[0-9]*'), db_column) != '' THEN COALESCE(substring(db_column FROM '[0-9]*'), db_column)
    ELSE db_column
END AS addsress_string
FROM db_table

Answer 1

I don't know postgresql regex syntax, but in most regex you would write [0-9]+. Without the quantifier, [0-9] matches a single character.