数据清洗全角半角转换

发表于 分类于

乱七八糟的数据,基于PostgreSQL

自定义函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- 全角转半角
create or replace function full_to_half(text) returns text as  
$$  
select string_agg(col, '') from   
 ( select   
   case when ascii(col) >= 65281 and ascii(col) <=65374 then chr(ascii(col)-65248)  
        when ascii(col) = 12288 then chr(32)  
        else col  
   end AS col   
   from (select regexp_split_to_table($1, '') as col) t  
  ) tt;   
$$ language sql strict immutable; 

-- 半角转全角
create or replace function half_to_full(text) returns text as  
$$  
select string_agg(col, '') from   
 ( select   
   case when ascii(col) >= 33 and ascii(col) <=126 then chr(ascii(col)+65248)  
        when ascii(col) = 32 then chr(12288)  
        else col  
   end AS col   
   from (select regexp_split_to_table($1, '') as col) t  
  ) tt;   
$$ language sql strict immutable;