高效字符串替换

今天看到AnySQL提到了关于字符串的灵活替换一文.

1)
SELECT
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TABLE_NAME,'0','#'),
'1','#') ,'2','#') ,'3','#') ,'4','#')
,'5','#') ,'6','#') ,'7','#') ,'8','#') ,'9','#') PATTERN_NAME
FROM USER_TABLES;
2)
地铁上,AnySQL又想到了改进的方法,

SELECT
TRANSLATE(TABLE_NAME,'0123456789','##########') PATTERN_NAME
FROM USER_TABLES;

3)
还有更简洁的, 运用正则表达式 替换 或者 查找:

select REGEXP_REPLACE(TABLE_NAME, '[1-9]', '#' )
from cat;

在大数据量情况下(比如超过1百万行数据),需要注意一个问题: 就是函数REGEXP_InStr()
和PGA内存的使用.

update abedba.addresses
set
NAME = REGEXP_REPLACE(NAME, '['||CHR(13)||CHR(10)||']', ' ' )
where
REGEXP_InStr(NAME, '['||CHR(13)||CHR(10)||']' ) > 0;

ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 4000 bytes (kxs heap,kghalp : evarxalc)

work around 就是使用传统的InStr()函数:

update abedba.addresses
set &1 = REGEXP_REPLACE(&1, '['||CHR(13)||CHR(10)||']', ' ' )
where INSTR(&1, CHR(10)) > 0 or INSTR(&1, CHR(13)) > 0;

--最后一点,就是经验问题,
如果你有多个列做相同的修改, 最好用SQL*Plus变量替换column_name,存成脚本, 成批运行,
这样,就不用担心改错了数据, 比如把ADDRESS改成CITY.

错误样本:
update abedba.addresses
set
ADDRESS= REGEXP_REPLACE(CITY, '['||CHR(13)||CHR(10)||']', ' ' )
where
REGEXP_InStr(CITY, '['||CHR(13)||CHR(10)||']' ) > 0;

正确脚本:
-- C:\Charlie\DEV\scripts\deploy\2007apr10\addr_u.sql
-- http://mantis/jira/browse/OPS-2043

update abedba.addresses
set
&1 = REGEXP_REPLACE(&1, '['||CHR(13)||CHR(10)||']', ' ' )
where
INSTR(&1, CHR(10)) > 0 or INSTR(&1, CHR(13)) > 0;

/*
@C:\Charlie\DEV\scripts\deploy\2007apr10\addr_u.sql NAME
@C:\Charlie\DEV\scripts\deploy\2007apr10\addr_u.sql ADDRESS
@C:\Charlie\DEV\scripts\deploy\2007apr10\addr_u.sql ADDRESS2
@C:\Charlie\DEV\scripts\deploy\2007apr10\addr_u.sql CITY
@C:\Charlie\DEV\scripts\deploy\2007apr10\addr_u.sql PROVSTATE
@C:\Charlie\DEV\scripts\deploy\2007apr10\addr_u.sql COUNTRY
@C:\Charlie\DEV\scripts\deploy\2007apr10\addr_u.sql ZIPCODE
@C:\Charlie\DEV\scripts\deploy\2007apr10\addr_u.sql PHONE
*/

这是我的第一个技术帖, AnySQL不要说我盗版吆.

改进型:

SELECT * FROM(
SELECT TABLE_NAME, count(*) over (PARTITION BY REGEXP_REPLACE(TABLE_NAME, '[0-9]+', '#' ) ) AS tab_cnt
, REGEXP_REPLACE(TABLE_NAME, '[0-9]+', '#' ) tran_tab
FROM USER_TABLES
) WHERE tab_cnt > 1

运行结果:
TABLE_NAME TAB_CNT TRAN_TAB
-- -- ---
LOGMNR_HEADER2$ 2 LOGMNR_HEADER#$
LOGMNR_HEADER1$ 2 LOGMNR_HEADER#$
PARTS2 2 PARTS#
PARTS1 2 PARTS#
T101 4 T#
T2 4 T#
T1 4 T#
T3 4 T#

优点:
1) 两位数,3位数,n位数 都可以.
REGEXP_REPLACE(TABLE_NAME, '[1-9]', '#' )
更改成,
REGEXP_REPLACE(TABLE_NAME, '[1-9]+', '#' )
替换1到任意多个数字.

2) Count(*) over (PARTITION BY)
更容易理解, RATIO_TO_REPORT 分析函数有点用过了.

我最近正在学习 SQL Analytic Functions, 感谢 rollingpig 给出RATIO_TO_REPORT的这个用法.