<rt id="bn8ez"></rt>
<label id="bn8ez"></label>

  • <span id="bn8ez"></span>

    <label id="bn8ez"><meter id="bn8ez"></meter></label>

    Decode360's Blog

    業精于勤而荒于嬉 QQ:150355677 MSN:decode360@hotmail.com

      BlogJava :: 首頁 :: 新隨筆 :: 聯系 ::  :: 管理 ::
      302 隨筆 :: 26 文章 :: 82 評論 :: 0 Trackbacks
    ??? 在ITPUB上看到個帖子,計算四則運算的,順便來學習一下10g里的正則表達式:
    ?
    ??? 原帖地址:http://www.itpub.net/viewthread.php?tid=1051167&extra=page%3D1%26amp%3Bfilter%3Ddigest
    ?
    ??? 把樓主計算四則運算的SQL貼一下,加了一些我自己的注釋:
    ?

    select a.id,

    ?????? max (text) text,

    ?????? sum (regexp_substr(add_text, '[0-9]+' , 1 ,n) -- 依次找出第 N 個數字

    ?????????? *

    ?????????? decode(regexp_substr( '+' ||add_text, '[^0-9]' , 1 ,n), '+' , 1 ,- 1 )) -- 依次找出 +|- ,然后在后面的數字上乘以系數

    ?????? -- 以上 sum 計算了所有 +|- 運算的總合計值

    ?????? +

    ?????? nvl( sum (( select decode(substr(regexp_substr( '+' ||text, '[+|-]([0-9]+[*|/]+)+[0-9]+' , 1 ,n), 1 , 1 ), '+' , 1 ,- 1 )

    ???????????????????????????????????? -- 找出 +|- 開頭,并緊跟數字、 [*|/] 、數字的部分,依次根據第一位來判定系數

    ?????? *

    ?????? power( 10 , Sum ( Log ( 10 ,decode(regexp_substr( '*' ||regexp_substr(text, '([0-9]+[*|/]+)+[0-9]+' , 1 ,n), '[^0-9]' , 1 , rownum ),

    ???????????????????????????????????????????????????? -- 找出第 n 個數字、 [*|/] 、數字相連的部分

    ????????????????????????????????? -- 排除數字,找出前面找到的部分中的第 rownum 個非數字的字符 ( 最前面加 *)

    ????????????????????????? ???????? '*' ,

    ????????????????????????????????? regexp_substr(regexp_substr(text, '([0-9]+[*|/]+)+[0-9]+' , 1 ,n), '[0-9]+' , 1 , rownum ),

    ????????????????????????????????? -- 如果是 '*' 則,則直接找到 * 后面的數字部分

    ????????????????????????????????? 1 /regexp_substr(regexp_substr(text, '([0-9]+[*|/]+)+[0-9]+' , 1 ,n), '[0-9]+' , 1 , rownum )

    ????????????????????????????????? -- 如果不是 '*'( /) ,則用 1/NUM

    ????????????????????????????????? ))))

    ?????? -- 外層通關 LOG POWER 函數,把乘除法轉換為加減法

    ?????? from dual connect by rownum <=len) -- 在這里再做一層循環,用于計算乘除法

    ?????? ) , 0 ) wanted

    ? from ?

    ?????? ( select a.id,

    ?????????????? a.text,

    ?????????????? length(regexp_replace(text, '[0-9]+' ))+ 1 len, -- 去掉數字計算運算符個數

    ?????????????? regexp_replace(text, '([0-9]+[*|/]+)+[0-9]+' , 0 ) add_text -- *|/ 操作的數均用 0 代替

    ????????? from t_mar a) a,

    ?????? ( select rownum n from dual connect by rownum < 100 ) b

    ? where a.len>=b.n -- 可以直接形成從 1 a.len 的循環操作

    ? group by id

    ? order by id ;

    ?

    ?
    ??? 除了一些轉化、分類的思想之外,主要就是用到了正則表達式,再把Oracle 10g中的正則表達式規則也貼一下:
    ?

    \ ???? The backslash character can have four different meanings depending on

    ????? the context. It can:

    ?????? Stand for itself

    ?????? Quote the next character

    ?????? Introduce an operator

    ?????? Do nothing

    * ???? Matches zero or more occurrences

    + ???? Matches one or more occurrences

    ? ???? Matches zero or one occurrence

    | ???? Alternation operator for specifying alternative matches

    ^ ???? Matches the beginning of a string by default. In multiline mode, it matches

    ????? the beginning of any line anywhere within the source string.

    $ ???? Matches the end of a string by default. In multiline mode, it matches the

    ?? ???end of any line anywhere within the source string.

    . ???? Matches any character in the supported character set except NULL

    [ ] ?? Bracket expression for specifying a matching list that should match any

    ????? one of the expressions represented in the list. A nonmatching list

    ????? expression begins with a circumflex (^) and specifies a list that matches

    ????? any character except for the expressions represented in the list.

    ( ) ?? Grouping expression, treated as a single subexpression

    {m} ?? Matches exactly m times

    {m,} ? Matches at least m times

    {m,n} Matches at least m times but no more than n times

    \n ??? The backreference expression (n is a digit between 1 and 9) matches the nth

    ????? subexpression enclosed between '(' and ')' preceding the \n

    [..] ? Specifies one collation element, and can be a multicharacter element (for

    ????? example, [.ch.] in Spanish)

    [: :] Specifies character classes (for example, [:alpha:]). It matches any character

    ????? within the character class.

    [==] ? Specifies equivalence classes. For example, [=a=] matches all characters

    ????? having base letter 'a'.

    ?
    ?
    ??? 這樣就比較完整了,至于regexp_substrregexp_replace主要查詢《SQL Reference》就可以了
    ?
    ?




    -The End-

    posted on 2008-12-29 21:17 decode360-3 閱讀(174) 評論(0)  編輯  收藏 所屬分類: SQL Dev
    主站蜘蛛池模板: 亚洲AV无码久久精品蜜桃| 久久免费线看线看| 毛片免费视频观看| 亚洲精品日韩专区silk| 久久国产精品一区免费下载| 亚洲精品一级无码中文字幕| 国产成人亚洲精品91专区高清| 成年女人午夜毛片免费看| 亚洲狠狠成人综合网| 免费看成人AA片无码视频羞羞网| 亚洲国产亚洲片在线观看播放 | 一级做a免费视频观看网站| 国产精品二区三区免费播放心| 亚洲色大成网站www| 无码一区二区三区免费视频| 亚洲精品精华液一区二区| 免费毛片在线播放| 午夜在线亚洲男人午在线| 亚洲精品成人a在线观看| jizz免费观看视频| 亚洲成a人片在线观看中文动漫| a级毛片无码免费真人久久| 亚洲国产另类久久久精品| 99久在线国内在线播放免费观看 | 亚洲国产人成网站在线电影动漫| 丁香花在线观看免费观看图片| 国产成A人亚洲精V品无码性色| 中文字幕免费高清视频| 亚洲最大无码中文字幕| 免费在线观看黄网| 日韩电影免费观看| 亚洲欧美日韩久久精品| 亚洲A丁香五香天堂网| 九九美女网站免费| 亚洲jizzjizz在线播放久| 亚洲AV无码一区二区三区国产| 中文在线免费观看| 亚洲卡一卡二卡乱码新区| 国产精品亚洲高清一区二区| 91热成人精品国产免费| 久久久久久久久无码精品亚洲日韩|