木匠的微型博客 Charlie Twitter

    follow me on Twitter

    Tuesday, April 24, 2007

    高效字符串替换

    今天看到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的这个用法.

    1 comment:

    Anonymous said...

    Nice to meet you here. I am LuoBing from the ITClub in Victoria.