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

Question

This only gives me the last character (numeric), but I need an integer numeric string

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

• Results: 3
• Standby: 123

This gives me the same results, but I need to return an empty value:

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

• Results: 3
• Expectation:

NOTE: Postgres 7.4

Useful 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 are now returning: 123
• Still need to skip or return the full line: "123 Main Street"

UPDATE 2:

Almost he has:

This gives me the results I want if it does not start with a numerical 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 need 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 to everyone 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 do not know the syntax of regex postgresql, but in most regular expressions you write [0-9]+ . Without a quantifier, [0-9] matches a single character.

Answer 2

I don't have a Postgres installation for testing, but something like this might work:

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

This does not return anything unless it starts with a number. If you want to return the full string instead, this should work:

SELECT substring('123 Main Street' FROM '^[0-9]+|.*')

Answer 3

It might not be too late to improve rmmh's answer.

This works for me:

 SELECT substring(column_name FROM '[0-9]+') FROM TableName; 

In postgresql, simply remove the carriage to display the number.

if column_name = 'XXXX-0001' result will be:

 0001