Decode360's Blog

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 ;

\ ???? 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'.

select text,regexp_replace(text, '[0-9]' , 0 ) from t_mar -- 所有數字每個都用 0 代替

select text,regexp_replace(text, '[0-9]+' , 0 ) from t_mar -- 所有數字相鄰的用 1 個 0 代替

select text,regexp_replace(text, '[*|/]' , 0 ) from t_mar -- 所有 * 或 / 每個都用 0 代替

select text,regexp_replace(text, '[*|/]+' , 0 ) from t_mar -- 所有 * 或 / 相鄰的用 1 個 0 代替

select text,regexp_replace(text, '[0-9]+[*|/]+' , 0 ) from t_mar -- 數字和 * 或數字和 / 相鄰的用 1 個 0 代替

select text,regexp_replace(text, '([0-9]+[*|/]+)+[0-9]+' , 0 ) from t_mar -- 數字和 *|/ 和數字相鄰的用 1 個 0 代替

select text,regexp_replace(text, '[^0-9]' , 0 ) from t_mar -- 除了數字別的字符每個都用 0 代替

select text,regexp_substr(text, '([0-9]+[*|/]+)+[0-9]+' , 1 , 1 ) from t_mar -- 找到第 1 個數字、 [*|/] 、數字相連的部分

select regexp_substr( '34*66_50#$@(97)5' , '[^0-9]' , 2 , 4 ) from dual -- 找到從第 2 位開始的第 4 個非數字字符

?

?