如何从数据表列提取街道编号并新增Streetnum列存储？

根据你的数据特征，街道编号是8位连续数字，以下是主流数据库的实现方案：

Oracle数据库 #

1. 先添加新列：

ALTER TABLE your_table ADD Streetnum VARCHAR2(8);

2. 提取并更新街道编号：

UPDATE your_table
SET Streetnum = REGEXP_SUBSTR(your_column, '(\d{8})', 1, 1, '', 1);

如果需要精准匹配Streetnum前缀后的编号（避免其他无关8位数字），可以调整正则：

UPDATE your_table
SET Streetnum = REGEXP_SUBSTR(your_column, 'Streetnum[_]?(\d{8})', 1, 1, '', 1);

MySQL 8.0+ #

1. 添加新列：

ALTER TABLE your_table ADD Streetnum VARCHAR(8);

2. 提取并更新：

UPDATE your_table
SET Streetnum = REGEXP_SUBSTR(your_column, '[0-9]{8}');

精准匹配前缀版本：

UPDATE your_table
SET Streetnum = REGEXP_SUBSTR(your_column, 'Streetnum[_]?([0-9]{8})', 1, 1, '', 1);

SQL Server #

1. 添加新列：

ALTER TABLE your_table ADD Streetnum VARCHAR(8);

2. 提取并更新：

UPDATE your_table
SET Streetnum = SUBSTRING(your_column, PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', your_column), 8);

精准匹配前缀版本：

UPDATE your_table
SET Streetnum = SUBSTRING(your_column, PATINDEX('%Streetnum[_]?[0-9]{8}%', your_column) + LEN('Streetnum') + CASE WHEN SUBSTRING(your_column, PATINDEX('%Streetnum[_]?[0-9]{8}%', your_column) + LEN('Streetnum'), 1) = '_' THEN 1 ELSE 0 END, 8);

说明 #

• 请将your_table替换为你的表名，your_column替换为包含长描述的列名。
• 如果街道编号长度不是固定8位，需要调整正则中的数字位数（比如\d{6,8}匹配6-8位数字）。

内容的提问来源于stack exchange，提问作者saurav kumar