第一次出现数字时的SQL拆分字符串

Question

我的数据库中有一个包含街道和门牌号的字段。我想把房号分成一个新的栏目。我可以这样做吗？

INSERT INTO mytable(housenumber) VALUES SELECT ??? FROM mytable ?

Answer 1

最简单的解决方案似乎是使用substring function with regular expressions。我希望你的PostgreSQL版本支持它们。

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)

正如NullUserException在评论中提到的那样，街道名称本身可能包含一个数字，这不应被视为门牌号码。在这种情况下，我认为“门牌号”可以定义为以数字开头，前面有一个空格的子字符串。

在这种情况下，正则表达式看起来是这样的：

SELECT adres,
       substring(adres from '^.*?(?=\\s[0-9]|$)') AS street,
       substring(adres from '\\s([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 | withnumber1     | 234
(5 rows)