如何通过单条Hive查询移除多列中的字符串及特殊字符

数据老陈

2026-5-20

Hive数据清洗：移除所有列中的非数字字符问题

原始数据表

YSQ YSQR ys Y
12 12 55 11
abc 22 qrs 2
# def @ aaa

预期处理结果

YSQ YSQR ys Y
12 12 55 11
11 22 33 2
2 22 11 33

你尝试的查询（未达预期）

select YSQ,YSQR,YS,Y from Sales_table2 where (YSRQTYPCS not rlike '[^0-9.-]') or YTTLSVAL not rlike '[^0-9.-]' or YSRTLSVAL not rlike '[^0-9.-]' or YSQTYPCS not rlike '[^0-9.-]'

问题分析&解决方案

你之前的思路走偏啦——用WHERE子句是在过滤整行数据，会直接丢掉包含非数字字符的行，但你的需求是保留所有行，只清洗每一列里的非数字/特殊字符。

正确的做法是对每个列使用regexp_replace函数，把非数字字符全部替换为空，只保留数字部分。下面是符合需求的单条Hive查询：

SELECT
  regexp_replace(YSQ, '[^0-9]', '') AS YSQ,
  regexp_replace(YSQR, '[^0-9]', '') AS YSQR,
  regexp_replace(ys, '[^0-9]', '') AS ys,
  regexp_replace(Y, '[^0-9]', '') AS Y
FROM Sales_table2;

补充说明：

如果替换后出现空字符串（比如原始第三行的# def清洗后为空），想要把它转换成0的话，可以用CASE语句处理：

SELECT
  CASE WHEN regexp_replace(YSQ, '[^0-9]', '') = '' THEN '0' ELSE regexp_replace(YSQ, '[^0-9]', '') END AS YSQ,
  CASE WHEN regexp_replace(YSQR, '[^0-9]', '') = '' THEN '0' ELSE regexp_replace(YSQR, '[^0-9]', '') END AS YSQR,
  CASE WHEN regexp_replace(ys, '[^0-9]', '') = '' THEN '0' ELSE regexp_replace(ys, '[^0-9]', '') END AS ys,
  CASE WHEN regexp_replace(Y, '[^0-9]', '') = '' THEN '0' ELSE regexp_replace(Y, '[^0-9]', '') END AS Y
FROM Sales_table2;