Gbase 8s内置函数之字符串函数

Gbase 8s的内置函数包含标量函数、聚合函数、窗口函数以及其他的函数。

其中标量函数又包含数学函数、字符串函数、日期函数。

本次介绍字符串函数及其使用例句:

CONCAT

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

TRIM/LTRIM/RTRIM

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;

 

SUBSTR/SUBSTRB/SUBSTRING

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

INSTR

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

ASCII

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;

REPLACE

select content, replace(content, 'reading', 'writing')  as f_replace

from

(select 'I like reading' as content from dual) t;

UPPER/LOWER

select upper('Hello World') as f_upper, lower('Hello World') as f_lower from dual;

LENGTH/OCTET_LENGTH/CHAR_LENGTH

select length('南大通用') as f_len11, length('南大通用:GBase') as f_len12 , octet_length('南大通用') as f_len21, octet_length('南大通用:GBase') as f_len22, char_length('南大通用') as f_len31, char_length('南大通用:GBase)') as f_len3 from dual;

REGEXP_REPLACE/REGEXP_SUBSTR/REGEXT_INSTR

select f_content, regexp_replace(f_content, '\d{11}', '<phone_number>') as f_template from (select '我的电话号码是13920656789，我家的邮编是300160。' as f_content from dual union all select '快递已经给您放小区菜鸟驿站了，有问题打电话18632145678。' as f_content from dual) t;