木匠的微型博客 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的这个用法.

    Wednesday, April 11, 2007

    再谈在加拿大就医 - 看急诊

    昨天下午,改了夏时制以后,吃完晚饭,7:30pm,天光依然大亮,随即出门散步.
    街道依然有小猫徜徉,老婆依旧伏身逗玩猫咪,依旧不听我的忠言警告;
    (黑龙江老乡George的乖女儿被猫儿抓破脸的一幕依然印象深刻)
    事情发展的那么快,小猫翻身打滚的一瞬间,老婆的手上被划出一道3厘米长的
    血口子.

    幸好我们住的离Jubilee医院近,10分钟走到,
    进了急诊室,有向导,诊断,开始看似一切顺利,
    心里暗暗祷告,那些网上华人老乡流传的故事该不会发生在我们身上...

    初诊以后就在大厅等候医生治疗,
    等了两个小时,只有两个排在我们前面的人被叫进去,
    这才发现好多人是有备而来,有看小说的,有玩电脑的...
    一直等到午夜12点,才轮到我老婆,3分钟内打针吃药,看的到挺快.

    顺便看一下今天的星相,还是积极乐观的,吻合了我的心愿.
    You are an inventor, ready to change routines and improve the ordinary with slight but brilliant tweaks and modifications. That optimistic, hopeful spirit encourages and amazes those around you.