PostgreSQL正则表达式：从字符串提取地震震级数值

解决方案 #

你可以使用PostgreSQL的正则表达式函数提取震级数值，以下是两种常用方法：

1. 使用 substring 函数

substring 可直接通过正则表达式捕获目标部分：

SELECT 
  event_str,
  substring(event_str from '^(?:M|Md) (\d+\.\d+) -') AS magnitude
FROM earthquakes;

2. 使用 regexp_match 函数

regexp_match 返回匹配结果的数组，取第一个元素即可得到震级：

SELECT 
  event_str,
  (regexp_match(event_str, '^(?:M|Md) (\d+\.\d+) -'))[1] AS magnitude
FROM earthquakes;

正则表达式说明 #

• ^：匹配字符串开头
• (?:M|Md)：非捕获组，匹配前缀 "M" 或 "Md"
• ：匹配前缀后的空格
• (\d+\.\d+)：捕获组，匹配震级数值（包含整数和小数部分）
• -：匹配震级后的 " -" 分隔符

将提取结果存入新列 #

如果需要将震级永久存储为单独列，可执行以下步骤：

1. 添加数值类型列：

ALTER TABLE earthquakes ADD COLUMN magnitude numeric;

2. 更新列值：

UPDATE earthquakes 
SET magnitude = substring(event_str from '^(?:M|Md) (\d+\.\d+) -')::numeric;

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