更新时间:2024-03-06 GMT+08:00

字符处理函数和操作符

GaussDB(DWS)提供的字符处理函数和操作符主要用于字符串与字符串、字符串与非字符串之间的连接,以及字符串的模式匹配操作。

ascii(string)

描述:参数string的第一个字符的ASCII码。

返回值类型:integer

示例:

1
2
3
4
5
SELECT ascii('xyz');
 ascii 
-------
   120
(1 row)

bit_length(string)

描述:字符串的位数。

返回值类型:integer

示例:

1
2
3
4
5
SELECT bit_length('world');
 bit_length
------------
         40
(1 row)

btrim(string text [, characters text])

描述:从string开头和结尾删除只包含characters中字符(缺省是空白)的最长字符串。

返回值类型:text

示例:

1
2
3
4
5
SELECT btrim('sring' , 'ing');
 btrim
-------
 sr
(1 row)

char_length(string)或character_length(string)

描述:字符串中的字符个数。

返回值类型:integer

示例:

1
2
3
4
5
SELECT char_length('hello');
 char_length
-------------
           5
(1 row)

chr(integer)

描述:给出ASCII码的字符。

返回值类型:varchar

示例:

1
2
3
4
5
SELECT chr(65);
 chr
-----
 A
(1 row)

concat(str1,str2)

描述:将字符串str1和str2连接并返回。

  • ORA和TD兼容模式下,返回结果为所有非NULL字符串的连接。
  • MySQL兼容模式下,入参中存在NULL时,返回结果为NULL。

返回值类型:varchar

示例:

1
2
3
4
5
SELECT concat('Hello', ' World!');
    concat    
--------------
 Hello World!
(1 row)

concat_ws(sep text, str"any" [, str"any" [, ...] ])

描述:以第一个参数为分隔符,链接第二个以后的所有参数。

返回值类型:text

示例:

1
2
3
4
5
SELECT concat_ws(',', 'ABCDE', 2, NULL, 22);
 concat_ws
------------
 ABCDE,2,22
(1 row)

convert(string bytea, src_encoding name, dest_encoding name)

描述:以dest_encoding指定的目标编码方式转化字符串bytea。src_encoding指定源编码方式,在该编码下,string必须是合法的。

返回值类型:bytea

示例:

1
2
3
4
5
SELECT convert('text_in_utf8', 'UTF8', 'GBK');
          convert        
----------------------------
 \x746578745f696e5f75746638
(1 row)

如果源编码格式到目标编码格式的转化规则不存在,则字符串不进行任何转换直接返回,如GBK和LATIN1之间的转换规则是不存在的,具体转换规则可以通过查看系统表pg_conversion获得。

示例:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
show server_encoding;
 server_encoding 
-----------------
 LATIN1
(1 row)

SELECT convert_from('some text', 'GBK');
 convert_from 
--------------
 some text
(1 row)

db_latin1=# SELECT convert_to('some text', 'GBK');
      convert_to      
----------------------
 \x736f6d652074657874
(1 row)

db_latin1=# SELECT convert('some text', 'GBK', 'LATIN1');
       convert        
----------------------
 \x736f6d652074657874
(1 row)

convert_from(string bytea, src_encoding name)

描述:以数据库的编码方式转化字符串bytea。

src_encoding指定源编码方式,在该编码下,string必须是合法的。

返回值类型:text

示例:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
SELECT convert_from('text_in_utf8', 'UTF8');
 convert_from
--------------
 text_in_utf8
(1 row)
SELECT convert_from('\x6461746162617365','gbk');
 convert_from
--------------
 database
(1 row)

convert_to(string text, dest_encoding name)

描述:将字符串转化为dest_encoding的编码格式。

返回值类型:bytea

示例:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
SELECT convert_to('some text', 'UTF8');
      convert_to
----------------------
 \x736f6d652074657874
(1 row)
SELECT convert_to('database', 'gbk');
     convert_to
--------------------
 \x6461746162617365
(1 row)

decode(string text, format text)

描述:将二进制数据从文本数据中解码。

返回值类型:bytea

示例:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
SELECT decode('ZGF0YWJhc2U=', 'base64');
    decode    
--------------
 \x6461746162617365
(1 row)

SELECT convert_from('\x6461746162617365','utf-8');
 convert_from
--------------
 database
(1 row)

encode(data bytea, format text)

描述:将二进制数据编码为文本数据。

返回值类型:text

示例:

1
2
3
4
5
SELECT encode('database', 'base64');
  encode  
----------
 ZGF0YWJhc2U=
(1 row)

format(formatstr text [, str"any" [, ...] ])

描述:格式化字符串。

返回值类型:text

示例:

1
2
3
4
5
SELECT format('Hello %s, %1$s', 'World');
       format       
--------------------
 Hello World, World
(1 row)

instr(text,text,int,int)

描述:字符串匹配函数的位置,第一个int表示开始匹配起始位置,第二个int表示匹配的次数。

返回值类型:integer

示例:

1
2
3
4
5
SELECT instr( 'abcdabcdabcd', 'bcd', 2, 2 );
 instr
-------
     6
(1 row)

initcap(string)

描述:将字符串中的每个单词的首字母转化为大写,其他字母转化为小写。

返回值类型:text

示例:

1
2
3
4
5
SELECT initcap('hi THOMAS');
  initcap
-----------
 Hi Thomas
(1 row)

instr(string,substring[,position,occurrence])

描述:从字符串string的position(缺省时为1)所指的位置开始查找并返回第occurrence(缺省时为1)次出现子串substring的位置的值。

  • 当position为0时,返回0。
  • 当position为负数时,从字符串倒数第n个字符往前逆向搜索。n为position的绝对值。

本函数以字符为计算单位,如一个汉字为一个字符。

返回值类型:integer

示例:

1
2
3
4
5
SELECT instr('corporate floor','or', 3);
 instr 
-------
     5
(1 row)
1
2
3
4
5
SELECT instr('corporate floor','or',-3,2);
 instr 
-------
     2
(1 row)

lcase(string)

描述:把字符串转化为小写。

返回值类型:varchar

示例:

SELECT lcase('SAM');
 lcase
-------
 sam
(1 row)

left(str text, n int)

描述:返回字符串的前n个字符。

返回值类型:text
  • ORA和TD兼容模式下,当n是负数时,返回除最后|n|个字符以外的所有字符。
  • MySQL兼容模式下,当n是负数时,返回空串。

示例:

1
2
3
4
5
SELECT left('abcde', 2);
 left
------
 ab
(1 row)

length(string)

描述:获取参数string中字符的数目。

返回值类型:integer

示例:

1
2
3
4
5
SELECT length('abcd');
 length 
--------
      4
(1 row)

length(string bytea, encoding name )

描述:指定encoding编码格式的string的字符数。在这个编码格式中,string必须是有效的。

返回值类型:integer

示例:

1
2
3
4
5
SELECT length('jose', 'UTF8');
 length
--------
      4
(1 row)

lengthb(string)

描述:获取参数string中字节的数目。与字符集有关,同样的中文字符,在GBK与UTF8中,返回的字节数不同。

返回值类型:integer

示例:

1
2
3
4
5
SELECT lengthb('hello');
 lengthb 
---------
       5
(1 row)

lengthb(text/bpchar)

描述:获取指定字符串的字节数。

返回值类型:integer

示例:

1
2
3
4
5
SELECT lengthb('hello');
 lengthb
---------
       5
(1 row)
  • 若字符串中存在换行符,如字符串由一个换行符和一个空格组成,在GaussDB(DWS)中LENGTH和LENGTHB的值为2。
  • 对于CHAR(n) 类型,GaussDB(DWS)中n是指字符个数。因此,对于多字节编码的字符集, LENGTHB函数返回的长度可能大于n。

locate(substring,string[,position])

描述:从字符串string的position(缺省时为1)所指的位置开始查找并返回第一次出现子串substring位置的值。以字符为计算单位。当string中不存在substring时,返回0。

返回值类型:integer

示例:

1
2
3
4
5
SELECT locate('ball','football');
 locate 
--------
     5
(1 row)
1
2
3
4
5
SELECT locate('er','soccerplayer','6');
 locate 
--------
    11 
(1 row)

lower(string)

描述:把字符串转化为小写。

返回值类型:varchar

示例:

1
2
3
4
5
SELECT lower('TOM');
 lower
-------
 tom
(1 row)

lpad(string text, length int [, fill text])

描述:通过填充字符fill(缺省时为空白),把string填充为length长度。如果string已经比length长则将其尾部截断。

返回值类型:text

示例:

1
2
3
4
5
SELECT lpad('hi', 5, 'xyza');
 lpad  
-------
 xyzhi
(1 row)

lpad(string varchar, length int[, repeat_string varchar])

描述:在string的左侧添上一系列的repeat_string(缺省为空白)来组成一个总长度为n的新字符串。

如果string本身的长度比指定的长度length长,则本函数将把string截断并把前面长度为length的字符串内容返回。

返回值类型:varchar

示例:

1
2
3
4
5
SELECT lpad('PAGE 1',15,'*.');
      lpad       
-----------------
 *.*.*.*.*PAGE 1
(1 row)
1
2
3
4
5
SELECT lpad('hello world',5,'abcd');
 lpad  
-------
 hello
(1 row)

octet_length(string)

描述:字符串中的字节数。

返回值类型:integer

示例:

1
2
3
4
5
SELECT octet_length('jose');
 octet_length
--------------
            4
(1 row)

overlay(string placing string FROM int [for int])

描述:替换子字符串。FROM int表示从第一个string的第几个字符开始替换,for int表示替换第一个string的字符数目。

返回值类型:text

示例:

1
2
3
4
5
SELECT overlay('hello' placing 'world' from 2 for 3 );
 overlay 
---------
 hworldo
(1 row)

pg_client_encoding()

描述:当前客户端编码名称。

返回值类型:name

示例:

1
2
3
4
5
SELECT pg_client_encoding();
 pg_client_encoding
--------------------
 UTF8
(1 row)

position(substring in string)

描述:指定子字符串的位置。若string中没有substring,则返回0。

返回值类型:integer

示例:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
SELECT position('ing' in 'string');
 position
----------
        4
(1 row)

SELECT position('ing' in 'strin');
 position
----------
       0
(1 row)

quote_ident(string text)

描述:返回适用于SQL语句的标识符形式(使用适当的引号进行界定)。只有在必要的时候才会添加引号(字符串包含非标识符字符或者会转换大小写的字符)。返回值中嵌入的引号会被双写。

返回值类型:text

示例:

1
2
3
4
5
SELECT quote_ident('hello world');
 quote_ident
--------------
 "hello world"
(1 row)

quote_literal(string text)

描述:返回适用于在SQL语句里当作文本使用的形式(使用适当的引号进行界定)。

返回值类型:text

示例:

1
2
3
4
5
SELECT quote_literal('hello');
 quote_literal 
---------------
 'hello'
(1 row)

如果出现如下写法,text文本将进行转义。

1
2
3
4
5
SELECT quote_literal(E'O\'hello');
 quote_literal
---------------
 'O''hello'
(1 row)

如果出现如下写法,反斜杠会写入两次。

1
2
3
4
5
SELECT quote_literal('O\hello');
 quote_literal 
---------------
 E'O\\hello'
(1 row)

如果参数为NULL,返回空。如果参数可能为null,通常使用函数quote_nullable更适用。

1
2
3
4
5
SELECT quote_literal(NULL);
 quote_literal 
---------------

(1 row)

quote_literal(value anyelement)

描述:将给定的值强制转换为text,加上引号作为文本。

返回值类型:text

示例:

1
2
3
4
5
SELECT quote_literal(42.5);
 quote_literal 
---------------
 '42.5'
(1 row)

如果出现如下写法,定值将进行转义。

1
2
3
4
5
SELECT quote_literal(E'O\'42.5');
 quote_literal
---------------
 '0''42.5'
(1 row)

如果出现如下写法,反斜杠会写入两次。

1
2
3
4
5
SELECT quote_literal('O\42.5');
 quote_literal 
---------------
 E'O\\42.5'
(1 row)

quote_nullable(string text)

描述:返回适用于在SQL语句里当作字符串使用的形式(使用适当的引号进行界定)。

返回值类型:text

示例:

1
2
3
4
5
SELECT quote_nullable('hello');
 quote_nullable 
----------------
 'hello'
(1 row)

如果出现如下写法,text文本将进行转义。

1
2
3
4
5
SELECT quote_nullable(E'O\'hello');
 quote_nullable
----------------
 'O''hello'
(1 row)

如果出现如下写法,反斜杠会写入两次。

1
2
3
4
5
SELECT quote_nullable('O\hello');
 quote_nullable
----------------
 E'O\\hello'
(1 row)

如果参数为NULL,返回NULL。

1
2
3
4
5
SELECT quote_nullable(NULL);
 quote_nullable
----------------
 NULL
(1 row)

quote_nullable(value anyelement)

描述:将给定的参数值转化为text,加上引号作为文本。

返回值类型:text

示例:

1
2
3
4
5
SELECT quote_nullable(42.5);
 quote_nullable
----------------
 '42.5'
(1 row)

如果出现如下写法,定值将进行转义。

1
2
3
4
5
SELECT quote_nullable(E'O\'42.5');
 quote_nullable 
----------------
 'O''42.5'
(1 row)

如果出现如下写法,反斜杠会写入两次。

1
2
3
4
5
SELECT quote_nullable('O\42.5');
 quote_nullable
----------------
 E'O\\42.5'
(1 row)

如果参数为NULL,返回NULL。

1
2
3
4
5
SELECT quote_nullable(NULL);
 quote_nullable
----------------
 NULL
(1 row)

rawcat(raw,raw)

描述:字符串拼接函数。

返回值类型:raw

示例:
1
2
3
4
5
SELECT rawcat('ab','cd');
 rawcat
--------
 ABCD
(1 row)

regexp_like(source_string, pattern [, match_parameter])

描述:正则表达式的模式匹配函数。

source_string为源字符串,pattern为正则表达式匹配模式。 match_parameter为匹配选项,可取值为:

  • 'i':大小写不敏感。
  • 'c':大小写敏感。
  • 'n':允许正则表达式元字符“.”匹配换行符。
  • 'm':将source_string视为多行。

若忽略match_parameter选项,默认为大小写敏感,“.”不匹配换行符,source_string视为单行。

返回值类型:boolean

示例:

1
2
3
4
5
SELECT regexp_like('ABC', '[A-Z]');
 regexp_like
-------------
 t
(1 row)
1
2
3
4
5
SELECT regexp_like('ABC', '[D-Z]');
 regexp_like
-------------
 f
(1 row)
1
2
3
4
5
SELECT regexp_like('abc', '[A-Z]','i');
 regexp_like
-------------
 t
(1 row)
1
2
3
4
5
SELECT regexp_like('abc', '[A-Z]');
 regexp_like
-------------
 f
(1 row)

regexp_like(text,text,text)

描述:正则表达式的模式匹配函数。

返回值类型:bool

示例:

1
2
3
4
5
SELECT regexp_like('str','[ac]');
 regexp_like
-------------
 f
(1 row)

regexp_matches(string text, pattern text [, flags text])

描述:返回string中所有匹配POSIX正则表达式的子字符串。如果pattern不匹配,该函数不返回行。如果模式不包含圆括号子表达式,则每一个被返回的行都是一个单一元素的文本数组,其中包括匹配整个模式的子串。如果模式包含圆括号子表达式,该函数返回一个文本数组,它的第n个元素是匹配模式的第n个圆括号子表达式的子串。

flags参数为可选参数,包含零个或多个改变函数行为的单字母标记。i表示进行大小写无关的匹配,g表示替换每一个匹配的子字符串而不仅仅是第一个。

如果提供了最后一个参数,但参数值是空字符串(''),且数据库SQL兼容模式设置为ORA的情况下,会导致返回结果为空集。这是因为ORA兼容模式将''作为NULL处理,避免此类行为的方式有如下几种:

  • 将数据库SQL兼容模式改为TD;
  • 不提供最后一个参数,或最后一个参数不为空字符串。

返回值类型:setof text[]

示例:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
SELECT regexp_matches('foobarbequebaz', '(bar)(beque)');
 regexp_matches
----------------
 {bar,beque}
(1 row)
SELECT regexp_matches('foobarbequebaz', 'barbeque');
 regexp_matches 
----------------
 {barbeque}
(1 row)
 SELECT regexp_matches('foobarbequebazilbarfbonk', '(b[^b]+)(b[^b]+)', 'g');
    result    
--------------
 {bar,beque}
 {bazil,barf}
(2 rows)

regexp_replace(string, pattern, replacement [,flags ])

描述:替换匹配POSIX正则表达式的子字符串。 如果没有匹配pattern,那么返回不加修改的string串。 如果有匹配,则返回的string串里面的匹配子串将被replacement串替换掉。

replacement串可以包含\n, 其中\n是1到9, 表明string串里匹配模式里第n个圆括号子表达式的子串应该被插入, 并且它可以包含\&表示应该插入匹配整个模式的子串。

可选的flags参数包含零个或多个改变函数行为的单字母标记,见下表。

表1 flags参数的可选项

选项

描述

g

表示替换每一个匹配的子字符串而不仅仅是第一个(默认仅替换第一个匹配的子字符串)。

B

默认情况下使用Henry Spencer正则库及其正则语法。指定B选项后,表示优先选用boost regex正则库及其正则语法。

以下两种情况在指定了B选项时,也会自动转换为选择Henry Spencer正则库及其正则语法:

  • flags同时指定了p、q、w、x中的任意个字符。
  • string或pattern参数中含有多字节字符。

b

表示按照BRE(POSIX Basic Regular Expression)匹配模式的规则进行匹配。

c

大小写敏感匹配。

e

表示按照ERE(POSIX Extended Regular Expression)匹配模式的规则进行匹配。当b和e都未指定时,如果选用的是Henry Spencer正则库,则按照ARE(Advanced Regular Expression,类似于Perl Regular Expression)匹配模式的规则进行匹配;如果选用的是boost regex正则库,则按照Perl Regular Expression匹配模式的规则进行匹配。

i

大小写不敏感匹配。

m

换行敏感匹配,与选项n同义。

n

换行敏感匹配。此选项生效时,换行符影响元字符(.、^、$和[^)的匹配。

p

部分换行敏感匹配,此选项生效时,换行符影响元字符(.和[^)的匹配。部分是相对选项n而言。

q

重置正则表达式为加双引号的文本字符串,所有都是普通字符。

s

非换行敏感匹配。

t

紧凑语法(缺省)。该选项生效时,所有字符都很重要。

w

反部分换行敏感匹配,此选项生效时,换行符影响元字符(^和$)的匹配。部分是相对选项n而言。

x

扩展语法。与紧凑语法相对,在扩展的语法中,正则表达式中的空白字符被忽略。空白字符包括空格、水平制表符、新行、和任何属于space字符表的字符。

返回值类型:varchar

示例:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
SELECT regexp_replace('Thomas', '.[mN]a.', 'M');
 regexp_replace
----------------
 ThM
(1 row)
SELECT regexp_replace('foobarbaz','b(..)', E'X\\1Y', 'g') AS RESULT;                                                    
   result    
-------------
 fooXarYXazY
(1 row)

regexp_substr(text,text)

描述:正则表达式的抽取子串函数。与substr功能相似,正则表达式出现多个并列的括号时,也全部处理。

返回值类型:text

示例:

1
2
3
4
5
SELECT regexp_substr('str','[ac]');
 regexp_substr
---------------

(1 row)

regexp_split_to_array(string text, pattern text [, flags text ])

描述:用POSIX正则表达式作为分隔符,分隔string。和regexp_split_to_table相同,不过regexp_split_to_array会把它的结果以一个text数组的形式返回。

返回值类型:text[]

示例:

1
2
3
4
5
SELECT regexp_split_to_array('hello world', E'\\s+');
 regexp_split_to_array
-----------------------
 {hello,world}
(1 row)

regexp_split_to_table(string text, pattern text [, flags text])

描述:用POSIX正则表达式作为分隔符,分隔string。如果没有与pattern的匹配,该函数返回string。如果有至少有一个匹配,对每一个匹配它都返回从上一个匹配的末尾(或者串的开头)到这次匹配开头之间的文本。当没有更多匹配时,它返回从上一次匹配的末尾到串末尾之间的文本。

flags参数包含零个或多个改变函数行为的单字母标记。i表示进行大小写无关的匹配,g表示替换每一个匹配的子字符串而不仅仅是第一个。

返回值类型:setof text

示例:

1
2
3
4
5
6
SELECT regexp_split_to_table('hello world', E'\\s+');
 regexp_split_to_table
-----------------------
 hello
 world
(2 rows)

regexp_substr(source_char, pattern)

描述:正则表达式的抽取子串函数。

返回值类型:varchar

示例:

1
2
3
4
5
SELECT regexp_substr('500 Hello World, Redwood Shores, CA', ',[^,]+,') "REGEXPR_SUBSTR";
  REGEXPR_SUBSTR   
-------------------
 , Redwood Shores,
(1 row)

repeat(string text, number int )

描述:text

返回值类型:将string重复number次。

示例:

1
2
3
4
5
SELECT repeat('Pg', 4);
  repeat
----------
 PgPgPgPg
(1 row)

replace(string text, from text, to text)

描述:把字符串string里出现地所有子字符串from的内容替换成子字符串to的内容。

返回值类型:text

示例:
1
2
3
4
5
SELECT replace('abcdefabcdef', 'cd', 'XXX');
    replace     
----------------
 abXXXefabXXXef
(1 row)

replace(string varchar, search_string varchar, replacement_string varchar)

描述:把字符串string中所有子字符串search_string替换成子字符串replacement_string。

返回值类型:varchar

示例:

1
2
3
4
5
SELECT replace('jack and jue','j','bl');
    replace     
----------------
 black and blue
(1 row)

reverse(str)

描述:返回颠倒的字符串。

返回值类型:text

示例:

1
2
3
4
5
SELECT reverse('abcde');
 reverse
---------
 edcba
(1 row)

right(str text, n int)

描述:返回字符串中的后n个字符。

  • ORA和TD兼容模式下,当n是负数时,返回除前|n|个字符以外的所有字符。
  • MySQL兼容模式下,当n是负数时,返回空串。

返回值类型:text

示例:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
SELECT right('abcde', 2);
 right
-------
 de
(1 row)

SELECT right('abcde', -2);
 right 
-------
 cde
(1 row)

rpad(string varchar, length int [, fill varchar])

描述:使用填充字符fill(缺省时为空白),把string填充到length长度。如果string已经比length长则将其从尾部截断。

length参数在GaussDB(DWS)中表示字符长度。一个汉字长度计算为一个字符。

返回值类型:varchar

示例:

1
2
3
4
5
SELECT rpad('hi',5,'xyza');
 rpad
-------
 hixyz
(1 row)
1
2
3
4
5
SELECT rpad('hi',5,'abcdefg');
 rpad  
-------
 hiabc
(1 row)

rpad(string text, length int [, fill text])

描述:使用填充字符fill(缺省时为空白),把string填充到length长度。如果string已经比length长则将其从尾部截断。

返回值类型:text

示例:

1
2
3
4
5
SELECT rpad('hi', 5, 'xy');
 rpad
-------
 hixyx
(1 row)

rtrim(string text [, characters text])

描述:从字符串string的结尾删除只包含characters中字符(缺省是个空白)的最长的字符串。

返回值类型:text

示例:

1
2
3
4
5
SELECT rtrim('trimxxxx', 'x');
 rtrim
-------
 trim
(1 row)

rtrim(string [, characters])

描述:从字符串string的结尾删除只包含characters中字符(缺省是个空白)的最长的字符串。

返回值类型:varchar

示例:

1
2
3
4
5
SELECT rtrim('TRIMxxxx','x');
 rtrim
-------
 TRIM
(1 row)

ltrim(string [, characters])

描述:从字符串string的开头删除只包含characters中字符(缺省是一个空白)的最长的字符串。

返回值类型:varchar

示例:

1
2
3
4
5
SELECT ltrim('xxxxTRIM','x');
 ltrim
-------
 TRIM
(1 row)

string || string

描述:连接字符串。

返回值类型:text

示例:

1
2
3
4
5
SELECT 'DA'||'TABASE' AS RESULT;
 result 
--------
 DATABASE
(1 row)

string || non-string或non-string || string

描述:连接字符串和非字符串。

返回值类型:text

示例:

1
2
3
4
5
SELECT 'Value: '||42 AS RESULT;
  result   
-----------
 Value: 42
(1 row)

substring(string [from int] [for int])

描述:截取子字符串,from int表示从第几个字符开始截取,for int表示截取几个字节。

返回值类型:text

示例:

1
2
3
4
5
SELECT substring('Thomas' from 2 for 3);
 substring
-----------
 hom
(1 row)

substring(string from pattern)

描述:截取匹配POSIX正则表达式的子字符串。如果没有匹配它返回空值,否则返回文本中匹配模式的那部分。

返回值类型:text

示例:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
SELECT substring('Thomas' from '...$');
 substring
-----------
 mas
(1 row)
SELECT substring('foobar' from 'o(.)b');
 result 
--------
 o
(1 row)
SELECT substring('foobar' from '(o(.)b)');
 result 
--------
 oob
(1 row)

如果POSIX正则表达式模式包含任何圆括号,那么将返回匹配第一对子表达式(对应第一个左圆括号的) 的文本。如果想在表达式里使用圆括号而又不想导致这个例外,那么可以在整个表达式外边加上一对圆括号。

substring(string from pattern for escape)

描述:截取匹配SQL正则表达式的子字符串。声明的模式必须匹配整个数据串,否则函数失败并返回空值。为了标识在成功的时候应该返回的模式部分,模式必须包含逃逸字符的两次出现,并且后面要跟上双引号(")。匹配这两个标记之间的模式的文本将被返回。

返回值类型:text

示例:

1
2
3
4
5
SELECT substring('Thomas' from '%#"o_a#"_' for '#');
 substring
-----------
 oma
(1 row)

split_part(string text, delimiter text, field int)

描述:根据delimiter分隔string返回生成的第field个子字符串(从出现第一个delimiter的text为基础)。

返回值类型:text

示例:

1
2
3
4
5
SELECT split_part('abc~@~def~@~ghi', '~@~', 2);
 split_part
------------
 def
(1 row)

strpos(string, substring)

描述:指定的子字符串的位置。和position(substring in string)一样,不过参数顺序相反。

返回值类型:integer

示例:
1
2
3
4
5
SELECT strpos('source', 'rc');
 strpos
--------
      4
(1 row)

substrb(text,int,int)

描述:提取子字符串,第一个int表示提取的起始位置,第二个表示提取几个字节。

返回值类型:text

示例:

1
2
3
4
5
SELECT substrb('string',2,3);
 substrb
---------
 tri
(1 row)

substrb(text,int)

描述:提取子字符串,int表示提取的起始位置。

返回值类型:text

示例:

1
2
3
4
5
SELECT substrb('string',2);
 substrb
---------
 tring
(1 row)

sys_context ( 'namespace' , 'parameter')

描述:获取并返回指定namespace下参数parameter的值。

返回值类型:text

示例:

1
2
3
4
5
SELECT SYS_CONTEXT ( 'postgres' , 'archive_mode');
 sys_context
-------------

(1 row)

to_hex(number int or bigint)

描述:把number转换成十六进制表现形式。

返回值类型:text

示例:
1
2
3
4
5
SELECT to_hex(2147483647);
  to_hex
----------
 7fffffff
(1 row)

translate(string text, from text, to text)

描述:把在string中包含的任何匹配from中字符的字符转化为对应的在to中的字符。如果from比to长,删掉在from中出现的额外的字符。

返回值类型:text

示例:

1
2
3
4
5
SELECT translate('12345', '143', 'ax');
 translate
-----------
 a2x5
(1 row)

substr(string,from)

描述:

从参数string中抽取子字符串。

from表示抽取的起始位置。

  • from为0时,按1处理。
  • from为正数时,抽取从from到末尾的所有字符。
  • from为负数时,抽取字符串的后n个字符,n为from的绝对值。

返回值类型:varchar

示例:

from为正数时:

1
2
3
4
5
SELECT substr('ABCDEF',2);
 substr
--------
 BCDEF
(1 row)

from为负数时:

1
2
3
4
5
SELECT substr('ABCDEF',-2);
 substr
--------
 EF
(1 row)

substr(string,from,count)

描述:

从参数string中抽取子字符串。

from表示抽取的起始位置。

count表示抽取的子字符串长度。

  • from为0时,按1处理。
  • from为正数时,抽取从from开始的count个字符。
  • from为负数时,抽取从倒数第n个开始的count个字符,n为from的绝对值。
  • count小于1时,返回null。

返回值类型:varchar

示例:

from为正数时:

1
2
3
4
5
SELECT substr('ABCDEF',2,2);
 substr 
--------
 BC
(1 row)

from为负数时:

1
2
3
4
5
SELECT substr('ABCDEF',-3,2);
 substr 
--------
 DE
(1 row)

substrb(string,from)

描述:该函数和SUBSTR(string,from)函数功能一致,但是计算单位为字节。

返回值类型:bytea

示例:

1
2
3
4
5
SELECT substrb('ABCDEF',-2);
 substrb 
---------
 EF
(1 row)

substrb(string,from,count)

描述:该函数和SUBSTR(string,from,count)函数功能一致,但是计算单位为字节。

返回值类型:bytea

示例:

1
2
3
4
5
SELECT substrb('ABCDEF',2,2);
 substrb 
---------
 BC
(1 row)

string [NOT] LIKE pattern [ESCAPE escape-character]

描述:模式匹配函数。

如果pattern不包含百分号或者下划线,该模式只代表它本身,这时候LIKE的行为就像等号操作符。在pattern里的下划线(_)匹配任何单个字符;而一个百分号(%)匹配零或多个任何字符。

要匹配下划线或者百分号本身,在pattern里相应的字符必须前导逃逸字符。缺省的逃逸字符是反斜杠,但是用户可以用ESCAPE子句指定一个。要匹配逃逸字符本身,写两个逃逸字符。

返回值类型:boolean

示例:

1
2
3
4
5
SELECT 'AA_BBCC' LIKE '%A@_B%' ESCAPE '@' AS RESULT;
 result
--------
 t
(1 row)
1
2
3
4
5
SELECT 'AA_BBCC' LIKE '%A@_B%' AS RESULT;
 result
--------
 f
(1 row)
1
2
3
4
5
SELECT 'AA@_BBCC' LIKE '%A@_B%' AS RESULT;
 result
--------
 t
(1 row)

trim([leading |trailing |both] [characters] from string)

描述:从字符串string的开头、结尾或两边删除只包含characters中字符(缺省是一个空白)的最长的字符串。

返回值类型:varchar

示例:

1
2
3
4
5
SELECT trim(BOTH 'x' FROM 'xTomxx');
 btrim
-------
 Tom
(1 row)
1
2
3
4
5
SELECT trim(LEADING 'x' FROM 'xTomxx');
 ltrim
-------
 Tomxx
(1 row)
1
2
3
4
5
SELECT trim(TRAILING 'x' FROM 'xTomxx');
 rtrim
-------
 xTom
(1 row)

ucase(string)

描述:把字符串转化为大写。

返回值类型:varchar

示例:

SELECT ucase('sam');
 ucase
-------
 SAM
(1 row)

upper(string)

描述:把字符串转化为大写。

返回值类型:varchar

示例:

1
2
3
4
5
SELECT upper('tom');
 upper
-------
 TOM
(1 row)