SQL split string at first occurance of a number

I hava a field in my database containing street and housenumber. I want to split the housenumber in a new column. Can I do this by someting like

INSERT INTO mytable(housenumber) VALUES SELECT ??? FROM myta开发者_Python百科ble ?

The simplest solution seems to use substring function with regular expressions. I hope your version of PostgreSQL supports them.

SELECT adres,
       substring(adres from '^.*?(?=[0-9]|$)') AS street,
       substring(adres from '[0-9].*$') AS housenum
  FROM mytable;

      adres      |  street   |    housenum
-----------------+--------------+-----------------
 some string 12  | some string  | 12
 another 2c      | another      | 2c
 no number       | no number    | 
 99 first number |              | 99 first number
 withnumber1 234 | withnumber   | 1 234
(5 rows)

As NullUserException mentioned in the comment, the street name may contain a number itself, which should not be considered a house number. In this case I suppose that a "house number" may be defined as the substring starting with a digit, preceded by a space.

The regular expressions would in this case look this way:

SELECT adres,
       substring(adres from '^.*?(?=\\s[0-9]|$)') AS street,
       substring(adres from '\\s([0-9].*)$') AS housenum
  FROM mytable;

The examples will be then split differently:

      adres      |    street       | housenum
-----------------+-----------------+-----------
 some string 12  | some string     | 12
 another 2c      | another         | 2c
 no number       | no number       | 
 99 first number | 99 first number | 
 withnumber1 234 | withnumber1     | 234
(5 rows)