Snowflake中移除varchar列非数字字符生成10位左补零账号方法

核心处理逻辑分两步：

1. 剔除字段内所有非数字内容（字母、特殊符号等），仅保留0-9的数字字符
2. 对提取到的数字串做左侧补零，最终固定为10位长度，无有效数字时返回10个0

样例数据处理校验：

• 原始值00#9999999 → 提取数字得到009999999（9位）→ 补零后输出0009999999
• 原始值000123456M → 提取数字得到000123456（9位）→ 补零后输出0000123456
• 原始值N/A → 提取数字得到空串 → 补零后输出0000000000

各数据库实现写法 #

直接使用LPAD(Account_Number_Column,10,'0')未得到预期结果，是因为没有先清洗非数字脏数据，补零逻辑直接处理了带特殊字符、字母的原始值。以下是不同数据库可直接运行的SQL：

MySQL 8.0及以上版本 #

利用内置正则替换函数全局移除非数字字符，再做补零处理，无数字的场景会自动兜底为10个0：

SELECT
  LPAD(REGEXP_REPLACE(Account_Number_Column, '[^0-9]', ''), 10, '0') AS Expected_Result
FROM your_table_name;

语法说明：REGEXP_REPLACE(字段名, '[^0-9]', '')会把所有不属于0-9范围的字符全部替换为空，仅保留数字内容。

PostgreSQL #

PG的正则替换需要加'g'参数才会全局匹配替换所有非数字字符：

SELECT
  LPAD(REGEXP_REPLACE(Account_Number_Column, '[^0-9]', '', 'g'), 10, '0') AS Expected_Result
FROM your_table_name;

Oracle #

Oracle原生支持正则替换，写法和MySQL8.0基本一致：

SELECT
  LPAD(REGEXP_REPLACE(Account_Number_Column, '[^0-9]', ''), 10, '0') AS Expected_Result
FROM your_table_name;

注意：如果提取后的纯数字内容长度超过10位，上述写法会默认保留左侧前10位字符，若业务需要保留后10位或做其他截断规则，可在外层嵌套字符串截断函数调整。

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