SQL拆分字符串首次出现在数字上 [英] SQL split string at first occurance of a number

问题描述

我在数据库中有一个包含街道和门牌号的字段。我想在新列中分割门牌号码。我可以通过

  INSERT INTO mytable（housenumber）VALUES SELECT来做到这一点吗？来自mytable吗？ 
  

解决方案

最简单的解决方案似乎使用带有正则表达式的子字符串函数。希望您的PostgreSQL版本支持它们。

  SELECT adres，
 substring（adres来自'^。*？（？= [0-9] | $）'）AS街，
子串（来自'[0-9]。* $'的AS房屋号
 FROM mytable; 
  

  adres |街道| housenum 
 ----------------- + -------------- + ------------ ----- 
一些字符串12 |一些字符串| 12 
另外2c |另一个2c 
没有编号|没有数字
 99第一个数字| | 99第一个数字
 withnumber1 234 | withnumber | 1234 
（5行）
  

如 NullUserException 中所述在评论中，街道名称本身可能包含数字，不应将其视为门牌号码。在这种情况下，我假设可以将门牌号定义为以数字开头，后跟空格的子字符串。

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

  SELECT adres，
子字符串（来自'^。*的地址？ （？= \\s [0-9] | $）'）AS街，
子字符串（来自'\\s（[0-9]。*）$'的AS房屋
来自mytable； 
  

然后将示例分成不同的例子：

< preclass = lang-none prettyprint-override> adres |街道|房屋
----------------- + ----------------- + --------- -
一些字符串12 |一些字符串| 12
另外2c |另一个2c
没有编号|没有数字
99第一个数字| 99首个号码|
withnumber1 234 | withnumber1 | 234
（5行）


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 mytable ?

解决方案

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)

这篇关于SQL拆分字符串首次出现在数字上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！