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

Is this for sorting alphanumeric values that begin with numbers? — Matthew
– Matthew, Commented Apr 13, 2011 at 16:21
no I want to return a partial address if it starts with a numeric value else a full address — Phill Pafford
– Phill Pafford, Commented Apr 13, 2011 at 16:29

Justin Morgan · Accepted Answer · 2011-04-13 15:52:30Z

9

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.

answered Apr 13, 2011 at 15:52

Justin Morgan

30.7k13 gold badges82 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Phill Pafford Over a year ago

Hmm lead me to different syntax, +1

rmmh · Accepted Answer · 2011-04-13 16:15:34Z

5

I don't have a Postgres install to test with, but something like this might work:

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

That returns nothing if it doesn't start with a number. If you want to return the full string instead, this should work:

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

answered Apr 13, 2011 at 16:15

rmmh

7,12529 silver badges38 bronze badges

1 Comment

Phill Pafford Over a year ago

one field for both conditions in the same query?

AER 4AF · Accepted Answer · 2014-11-24 08:47:33Z

4

Maybe it is not yet too late to improve on rmmh's answer.

This works for me:

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

In postgresql, just remove the caret for the numbers to show.

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

edited Nov 24, 2014 at 8:47

user330315

answered Nov 24, 2014 at 8:45

AER 4AF

3953 silver badges11 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related