如何从SQL字符串右侧首空格后提取数值，非数值返回NULL

问题描述 #

需要从列x生成新列y，目标是提取字符串右侧第一个空格后的数值，若该位置无数值则返回NULL。原查询仅截取了空格后的内容，未区分数值与非数值：

SELECT x, SUBSTR(x, INSTR(x,' ', -1) + 1) AS y
FROM  <table_name>;

解决方案 #

根据不同SQL方言，提供两种常用实现方式：

方法1：使用正则匹配（适配MySQL、PostgreSQL等支持正则的数据库） #

通过CASE语句结合正则表达式判断截取内容是否为数值（支持整数和小数），符合条件则返回，否则返回NULL：

SELECT 
    x,
    CASE
        WHEN SUBSTR(x, INSTR(x,' ', -1) + 1) REGEXP '^[0-9]+(\.[0-9]+)?$'
        THEN SUBSTR(x, INSTR(x,' ', -1) + 1)
        ELSE NULL
    END AS y
FROM <table_name>;

方法2：使用TRY_CAST函数（适配SQL Server、PostgreSQL 12+等支持的数据库） #

利用TRY_CAST尝试将截取内容转换为数值类型，转换失败自动返回NULL，写法更简洁：

SELECT
    x,
    TRY_CAST(SUBSTR(x, INSTR(x,' ', -1) + 1) AS DECIMAL) AS y
FROM <table_name>;

说明 #

• 原查询的问题在于仅完成了字符串截取，未对截取结果的数值属性做校验。
• 正则表达式^[0-9]+(\.[0-9]+)?$可匹配整数（如123）和小数（如123.45），若仅需匹配整数，可简化为^[0-9]+$。
• TRY_CAST函数会自动处理转换失败的场景，无需额外判断，推荐在支持的数据库中使用。

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