GBase 8s 常用系统函数

GBase 8s 常用系统函数

标量函数

数学函数

函数	说明
abs	返回绝对值
ceil	返回大于参数的数值
floor	返回小于参数的数值
round	返回参数的四舍五入数值
mod	返回第一个参数的模
pow	计算数值的N次方
sqrt	计算平方根
root	计算数值的N次方根
exp	计算指数
ln	计算自然对数
logn	计算自然对数
log10	计算以10为底的对数
sin	计算正弦值
cos	计算余弦值

ABS

abs(num)

select abs(-5.6) as num1, abs(5.6) as num2 from dual;

> select abs(-5.6) as num1, abs(5.6) as num2 from dual;


            num1             num2 

5.60000000000000 5.60000000000000

1 row(s) retrieved.

> 

CEIL/FLOOR/ ROUND

ceil(num)

floor(num)

round(num)

select ceil(5.6) as f_ceil, floor(5.6)as f_floor, round(5.6) as f_round from dual;

> select ceil(5.6) as f_ceil, floor(5.6)as f_floor, round(5.6) as f_round from dual;


          f_ceil          f_floor          f_round 

               6                5                6

1 row(s) retrieved.

> 

select ceil(5.3) as f_ceil, floor(5.3)as f_floor, round(5.3) as f_round from dual;

> select ceil(5.3) as f_ceil, floor(5.3)as f_floor, round(5.3) as f_round from dual;


          f_ceil          f_floor          f_round 

               6                5                5

1 row(s) retrieved.

> 

select ceil(-5.6) as f_ceil, floor(-5.6)as f_floor, round(-5.6) as f_round from dual;

> select ceil(-5.6) as f_ceil, floor(-5.6)as f_floor, round(-5.6) as f_round from dual;


          f_ceil          f_floor          f_round 

              -5               -6               -6

1 row(s) retrieved.

> 

select ceil(-5.3) as f_ceil, floor(-5.3)as f_floor, round(-5.3) as f_round from dual;

> select ceil(-5.3) as f_ceil, floor(-5.3)as f_floor, round(-5.3) as f_round from dual;


          f_ceil          f_floor          f_round 

              -5               -6               -5

1 row(s) retrieved.

> 

MOD

mod(num)

select mod(10, 3) as f_mod from dual;

> select mod(10, 3) as f_mod from dual;


      f_mod 

          1

1 row(s) retrieved.

> 

POW/SQRT/ROOT

pow(num1, num2)

sqrt(num)

root(num1, num2)

select pow(2, 3) as f_pow3, sqrt(25) as f_sqrt, root(64, 3) as f_root from dual;

> select pow(2, 3) as f_pow3, sqrt(25) as f_sqrt, root(64, 3) as f_root from dual;


        f_pow3         f_sqrt         f_root 

8.000000000000 5.000000000000 4.000000000000

1 row(s) retrieved.

> 

EXP

exp(num)

> select exp(1) as f_exp from dual;


         f_exp 

2.718281828459

1 row(s) retrieved.

> 

LN/LOGN/LOG10

ln(num)

logn(num)

log10(num)

select ln(2.718281828459) as f_ln, logn(2.718281828459) as f_logn, log10(1000) as f_log10 from dual;

> select ln(2.718281828459) as f_ln, logn(2.718281828459) as f_logn, log10(1000) as f_log10 from dual;


          f_ln         f_logn        f_log10 

1.000000000000 1.000000000000 3.000000000000

1 row(s) retrieved.

> 

SIN/COS

sin(num)

cos(num)

select sin(1) as f_sin, cos(1) as f_cos from dual;

> select sin(1) as f_sin, cos(1) as f_cos from dual;


         f_sin          f_cos 

0.841470984808 0.540302305868

1 row(s) retrieved.

> 

字符串函数

函数	说明
CONCAT	字符串拼接
TRIM	从字符串的开头或结尾移除指定字符
LTRIM	从字符串的开头移除指定字符
RTRIM	从字符串的结尾移除指定字符
SUBSTR	截取字符串
SUBSTRB	截取字符串
SUBSTRING	截取字符串
INSTR	返回字符串中指定子串的开始位置
ASCII	返回字符串第一个字符的编码
REPLACE	替换字符串中的部分内容
UPPER	将字符串的字母转成大写
LOWER	将字符串的字母转成小写
LENGTH	返回字符串的长度
OCTET_LENGTH	返回字符串的长度
CHAR_LENGTH	返回字符串的长度
REGEXP_REPLACE	使用正则表达式方式替换字符串中的内容
REGEXP_SUBSTR	使用正则表达式方式替换字符串中的内容
REGEXT_INSTR	计算字符串中指定正则表达式定义的字符串所在位置

CONCAT

concat(str1, str2)

select concat('Hello', 'World') as f_concat from dual;

> select concat('Hello', 'World') as f_concat from dual;

f_concat   

HelloWorld

1 row(s) retrieved.

> 

TRIM/LTRIM/RTRIMtrim(str)

trim(both ‘char’ from column_name)

ltrim(str)

ltrim(str, ‘char’)

rtrim(str)

rtrim(str, ‘char’)

select f_message,
    octet_length(f_message) as f_len1, 
    octet_length(trim(f_message)) as f_len2,
    octet_length(ltrim(f_message)) as f_len3,
    octet_length(rtrim(f_message)) as f_len4
from (select ' Hello world    ' as f_message from dual) t;

> select f_message,
    octet_length(f_message) as f_len1, 
    octet_length(trim(f_message)) as f_len2,
    octet_length(ltrim(f_message)) as f_len3,
    octet_length(rtrim(f_message)) as f_len4
from (select ' Hello world    ' as f_message from dual) t;


f_message             f_len1      f_len2      f_len3      f_len4 

 Hello world              16          11          15          12

1 row(s) retrieved.

> 

select f_message, 
    octet_length(f_message) as f_len1,
    octet_length(trim(both '#' from f_message)) as f_len2,
    octet_length(ltrim(f_message, '#')) as f_len3,
    octet_length(rtrim(f_message, '#')) as f_len4
from 
(select '#Hello world####' as f_message from dual) t;

> select f_message, 
    octet_length(f_message) as f_len1,
    octet_length(trim(both '#' from f_message)) as f_len2,
    octet_length(ltrim(f_message, '#')) as f_len3,
    octet_length(rtrim(f_message, '#')) as f_len4
from 
(select '#Hello world####' as f_message from dual) t;


f_message             f_len1      f_len2      f_len3      f_len4 

#Hello world####          16          11          15          12

1 row(s) retrieved.

> 

SUBSTR/SUBSTRB/SUBSTRING

substr(str, start, len)

substrb(str, start, len)

substring(str from start for len)

select substr('abcdefg', 2, 3) as f_substr from dual;

INSTR

instr(str1, str2, start, count)

select instr('How are you?', 'o', 1, 1) as f_instr1, instr('How are you?', 'o', 1, 2) as f_instr2 from dual;

> select instr('How are you?', 'o', 1, 1) as f_instr1, instr('How are you?', 'o', 1, 2) as f_instr2 from dual;


   f_instr1    f_instr2 

          2          10

1 row(s) retrieved.

> 

ASCII

ascii(str)

select ascii('Hello') as f_ascii1, ascii('world') as f_ascii2, ascii('冀辉') as f_ascii3 from dual;
select ascii('冀') as f_ascii1, ascii('辉') as f_ascii2, ascii('冀辉') as f_ascii3 from dual;

> select ascii('Hello') as f_ascii1, ascii('world') as f_ascii2, ascii('冀辉