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

sql regex postgresql conditional

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.

sql regex postgresql conditional

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]+|.*')

sql regex postgresql conditional

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:

CodeHunter