SQL中实现纯数字客户号转数字、非纯数字保留字符串的方法（ISNUMERIC不可用）

这个需求我碰到过好几次，刚好不同数据库的字符串校验逻辑不一样，再加上你没法用ISNUMERIC，我给你分主流数据库整理可行的方案：

MySQL/MariaDB 方案 #

可以用正则匹配REGEXP来判断是否是纯数字，再结合CASE语句做条件转换：

SELECT 
    CASE 
        WHEN customer_id REGEXP '^[0-9]+$' THEN CAST(customer_id AS UNSIGNED)
        ELSE customer_id
    END AS processed_customer_id
FROM your_table;

这里^[0-9]+$表示字符串从头到尾都是数字，满足条件就转成无符号整数，不满足就保留原字符串。如果担心列类型兼容问题，也可以拆成两个列分别存储数字和字符串类型的值：

SELECT 
    CASE WHEN customer_id REGEXP '^[0-9]+$' THEN CAST(customer_id AS UNSIGNED) ELSE NULL END AS numeric_customer_id,
    CASE WHEN customer_id REGEXP '^[0-9]+$' THEN NULL ELSE customer_id END AS string_customer_id
FROM your_table;

SQL Server 方案 #

如果你的查询构建器支持TRY_CAST/TRY_CONVERT的话，这两个函数是最优解——它们会尝试转换，失败就返回NULL，配合CASE就能轻松实现需求：

SELECT 
    CASE 
        WHEN TRY_CAST(customer_id AS INT) IS NOT NULL THEN TRY_CAST(customer_id AS INT)
        ELSE customer_id
    END AS processed_customer_id
FROM your_table;

要是TRY_CAST也没法用，那就用PATINDEX匹配非数字字符，只要找不到非数字字符就是纯数字：

SELECT 
    CASE 
        WHEN PATINDEX('%[^0-9]%', customer_id) = 0 THEN CAST(customer_id AS INT)
        ELSE customer_id
    END AS processed_customer_id
FROM your_table;

%[^0-9]%表示匹配任意包含非数字的字符串，返回值为0说明没有非数字字符，是纯数字。

PostgreSQL 方案 #

PostgreSQL支持正则匹配操作符~，或者用TRY_CAST函数，两种方式都能解决：

正则匹配方式 #

SELECT 
    CASE 
        WHEN customer_id ~ '^[0-9]+$' THEN customer_id::INTEGER
        ELSE customer_id
    END AS processed_customer_id
FROM your_table;

TRY_CAST 简化方式 #

用COALESCE取第一个非NULL值，转换成功就用数字，失败就保留原字符串：

SELECT 
    COALESCE(TRY_CAST(customer_id AS INTEGER), customer_id) AS processed_customer_id
FROM your_table;

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