GBASE南大通用GBase 8s数据库常用函数-字符串函数

GBASE南大通用分享：GBase 8s数据库

字符串函数

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/RTRIM

trim(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

substr(str, start, len)

substrb(str, start, 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('南大通用') as f_ascii3 from dual;


   f_ascii1    f_ascii2    f_ascii3 

         72         119     8423141

1 row(s) retrieved.

> 

> select hex(ascii('南')) as f_ascii1, hex(ascii('大')) as f_ascii2, hex(ascii('南大通用')) as f_ascii3 from dual;


f_ascii1   f_ascii2   f_ascii3   

0x008086E5 0x0089BEE8 0x008086E5

1 row(s) retrieved.

> 

8423141 == H 80 86E5

[gbasedbt@devsvr ~]$ cat a.txt 
冀辉
[gbasedbt@devsvr ~]$ file a.txt 
a.txt: UTF-8 Unicode text
[gbasedbt@devsvr ~]$ od -x a.txt 
0000000 86e5 e880 89be 000a
0000007
[gbasedbt@devsvr ~]$ 

REPLACE

replace(str1, str2, str3)

select content, replace(content, 'reading', 'writing')  as f_replace 
from
(select 'I like reading' as content from dual) t;

> select content, replace(content, 'reading', 'writing')  as f_replace 
> from
> (select 'I like reading' as content from dual) t;


content        f_replace      

I like reading I like writing

1 row(s) retrieved.

> 

GBASE南大通用分享：UPPER/LOWER

upper(str)

lower(str)

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

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


f_upper     f_lower     

HELLO WORLD hello world

1 row(s) retrieved.

> 

GBASE南大通用分享：LENGTH/OCTET_LENGTH/CHAR_LENGTH

length(str)

octet_length(str)

char_length(str)

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;

> 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_len32 from dual;


    f_len11     f_len12     f_len21     f_len22     f_len31      f_len32 

         12          18          12          18           4          11

1 row(s) retrieved.

> 

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;

> 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;

f_content   我的电话号码是13920656789，我家的邮编是300160。
f_template  我的电话号码是<phone_number>，我家的邮编是300160。 

f_content   快递已经给您放小区菜鸟驿站了，有问题打电话1863
            2145678。
f_template  快递已经给您放小区菜鸟驿站了，有问题打电话<pho
            ne_number>。 

2 row(s) retrieved.

> 

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