GBASE 8C——SQL参考 2 函数和操作符

函数和操作符

GBase 8c提供了大量的函数和操作符，用户也可以自己定义函数和操作符。

逻辑操作符

常用的逻辑操作符：

AND

OR

NOT

比较操作符

操作符	描述
<	小于
>	大于
<=	小于等于
>=	大于等于
=	等于
<> or !=	不等于

1. 1. 1. 数学函数和操作符

下表展示了所有GBase 8c可用的数学操作符：

操作符	描述	例子	结果
+	加	2 + 3	5
-	减	2 - 3	-1
*	乘	2 * 3	6
/	除（整数除法截断结果）	4 / 2	2
%	模（取余）	5 % 4	1
^	指数	2.0 ^ 3.0	8
|/	平方根	|/ 25.0	5
||/	立方根	||/ 27.0	3
!	阶乘	5 !	120
!!	阶乘（前缀操作符）	!! 5	120
@	绝对值	@ -5.0	5
&	按位与	91 & 15	11
|	按位或	32 | 3	35
#	按位异或	17 # 5	20
~	按位求反	~1	-2
<<	按位左移	1 << 4	16
>>	按位右移	8 >> 2	2

可用的数学函数如下表：

函数	返回类型	描述	例子	结果
abs(x)	（和输入相同）	绝对值	abs(-17.4)	17.4
cbrt(dp)	dp	立方根	cbrt(27.0)	3
ceil(dp or numeric)	（和输入相同）	不小于参数的最小整数	ceil(-42.8)	-42
ceiling(dp or numeric)	（和输入相同）	不小于参数的最小整数（ceil的别名）	ceiling(-95.3)	-95
degrees(dp)	dp	把弧度转为角度	degrees(0.5)	28.6478897565412
div(y numeric, x numeric)	numeric	y/x的整数商	div(9,4)	2
exp(dp or numeric)	（和输入相同）	指数	exp(1.0)	2.71828182845905
floor(dp or numeric)	（和输入相同）	不大于参数的最大整数	floor(-42.8)	-43
ln(dp or numeric)	（和输入相同）	自然对数	ln(2.0)	0.693147180559945
log(dp or numeric)	（和输入相同）	以10为底的对数	log(100.0)	2
log(b numeric, x numeric)	numeric	以b为底的对数	log(2.0, 64.0)	6.0000000000
mod(y, x)	（和参数类型相同）	y/x的余数	mod(9,4)	1
pi()	dp	“π”常数	pi()	3.14159265358979
power(a dp, b dp)	dp	求a的b次幂	power(9.0, 3.0)	729
power(a numeric, b numeric)	numeric	求a的b次幂	power(9.0, 3.0)	729
radians(dp)	dp	把角度转为弧度	radians(45.0)	0.785398163397448
round(dp or numeric)	（和输入相同）	圆整为最接近的整数	round(42.4)	42
round(v numeric, s int)	numeric	圆整为s位小数数字	round(42.4382, 2)	42.44
scale(numeric)	integer	参数的精度（小数点后的位数）	scale(8.41)	2
sign(dp or numeric)	（和输入相同）	参数的符号（-1, 0, +1）	sign(-8.4)	-1
sqrt(dp or numeric)	（和输入相同）	平方根	sqrt(2.0)	1.4142135623731
trunc(dp or numeric)	（和输入相同）	截断（向零靠近）	trunc(42.8)	42
trunc(v numeric, s int)	numeric	截断为s位小数位置的数字	trunc(42.4382, 2)	42.43
width_bucket(operand dp, b1 dp, b2 dp, count int)	int	返回一个桶，这个桶是在一个有count个桶， 上界为b1，下界为b2的柱图中operand将被赋予的那个桶。为外部范围输入返回0或者count+1	width_bucket(5.35, 0.024, 10.06, 5)	3
width_bucket(operand numeric, b1 numeric, b2 numeric, count int)	int	返回一个桶，这个桶是在一个有count个桶，上界为b1，下界为b2的柱图中operand将被赋予的那个桶； 为范围外的输入返回0或者count+1	width_bucket(5.35, 0.024, 10.06, 5)	3
width_bucket(operand anyelement, thresholds anyarray)	int	返回一个桶，它是给定数组列出桶的下限operand将被赋予的那个桶， 为了输入低于第一下界返回0；thresholds数组必须被存储， 首先最小值，或者获取意想不到的结果	width_bucket(now(), array['yesterday', 'today', 'tomorrow']::timestamptz[])	2

表中dp表示double precision。除非特殊指明，任何函数都返回和它参数相同的数据类型。

GBase 8c数据库产生随机数的函数如下：

函数	返回类型	描述
random()	dp	范围 0.0 <= x < 1.0 中的随机值
setseed(dp)	void	为后续的random()调用设置种子（值为于 -1.0 和 1.0 之间，包括边界值）

三角函数如下：

函数 (弧度)	函数 (角度)	描述
acos(x)	acosd(x)	反余弦
asin(x)	asind(x)	反正弦
atan(x)	atand(x)	反正切
atan2(y, x)	atan2d(y, x)	y/x的反正切
cos(x)	cosd(x)	余弦
cot(x)	cotd(x)	余切
sin(x)	sind(x)	正弦
tan(x)	tand(x)	正切

1. 1. • 字符串函数和操作符

SQL定义的字符串函数，使用关键字而不是逗号来分隔参数，如下表所示：

函数	返回类型	描述	例子	结果
string || string	text	串接	'Post' || 'greSQL'	PostgreSQL
string || non-string or non-string || string	text	使用一个非字符串输入的串接	'Value: ' || 42	Value: 42
bit_length(string)	int	串中的位数	bit_length('jose')	32
char_length(string) or character_length(string)	int	串中字符数	char_length('jose')	4
lower(string)	text	将字符串转换为小写形式	lower('TOM')	tom
octet_length(string)	int	串中的字节数	octet_length('jose')	4
overlay(string placing string from int [for int])	text	替换子串	overlay('Txxxxas' placing 'hom' from 2 for 4)	Thomas
position(substring in string)	int	定位指定子串	position('om' in 'Thomas')	3
substring(string [from int] [for int])	text	提取子串	substring('Thomas' from 2 for 3)	hom
substring(string from pattern)	text	提取匹配POSIX正则表达式的子串。	substring('Thomas' from '...$')	mas
substring(string from pattern for escape)	text	提取匹配SQL正则表达式的子串。	substring('Thomas' from '%#"o_a#"_' for '#')	oma
trim([leading | trailing | both] [characters] from string)	text	从string的开头、结尾、两端（默认是both） 删除最长的只包含来自characters字符（默认是一个空格）的串	trim(both 'xyz' from 'yxTomxx')	Tom
trim([leading | trailing | both] [from] string [, characters] )	text	trim()的非标准语法	trim(both from 'yxTomxx', 'xyz')	Tom
upper(string)	text	将字符串转换成大写形式	upper('tom')	TOM

其他字符串函数：

函数	返回类型	描述	例子	结果
ascii(string)	int	参数第一个字符的ASCII代码。对于UTF8返回该字符的Unicode代码点。对于其他多字节编码，该参数必须是一个ASCII字符。	ascii('x')	120
btrim(string text [, characters text])	text	从string的开头或结尾删除最长的只包含characters（默认是一个空格）的串	btrim('xyxtrimyyx', 'xyz')	trim
chr(int)	text	给定代码的字符。对于UTF8该参数被视作一个Unicode代码点。对于其他多字节编码该参数必须指定一个ASCII字符。NULL (0) 字符不被允许，因为文本数据类型不能存储这种字节。	chr(65)	A
concat(str "any" [, str "any" [, ...] ])	text	串接所有参数的文本表示。NULL 参数被忽略。	concat('abcde', 2, NULL, 22)	abcde222
concat_ws(sep text, str "any" [, str "any" [, ...] ])	text	将除了第一个参数外的其他参数用分隔符串接在一起。第一个参数被用作分隔符字符串。NULL 参数被忽略。	concat_ws(',', 'abcde', 2, NULL, 22)	abcde,2,22
convert(string bytea, src_encoding name, dest_encoding name)	bytea	将字符串转换为dest_encoding。原始编码由src_encoding指定。string在这个编码中必须可用。转换可以使用CREATE CONVERSION定义。也有一些预定义的转换。	convert('text_in_utf8', 'UTF8', 'LATIN1')	用Latin-1编码(ISO 8859-1) 表示的text_in_utf8
convert_from(string bytea, src_encoding name)	text	将字符串转换为数据库编码。原始编码由src_encoding指定。string在这个编码中必须可用。	convert_from('text_in_utf8', 'UTF8')	用当前数据库编码表示的text_in_utf8
convert_to(string text, dest_encoding name)	bytea	将字符串转换为dest_encoding。	convert_to('some text', 'UTF8')	用UTF8编码表达的some text
decode(string text, format text)	bytea	从string中的文本表达解码二进制数据。format的选项和encode中的一样。	decode('MTIzAAE=', 'base64')	\x3132330001
encode(data bytea, format text)	text	将二进制数据编码成一个文本表达。支持的格式有：base64、hex、escape。escape将零字节和高位组字节转换为八进制序列（\nnn）和双写的反斜线。	encode(E'123\\000\\001', 'base64')	MTIzAAE=
format(formatstr text [, formatarg "any" [, ...] ])	text	根据一个格式字符串格式化参数。该函数和C函数sprintf相似。	format('Hello %s, %1$s', 'World')	Hello World, World
initcap(string)	text	将每一个词的第一个字母转换为大写形式并把剩下的字母转换为小写形式。词是由非字母数字字符分隔的字母数字字符的序列。	initcap('hi THOMAS')	Hi Thomas
left(str text, n int)	text	返回字符串中的前n个字符。当n为负时，将返回除了最后|n|个字符之外的所有字符。	left('abcde', 2)	ab
length(string)	int	string中的字符数	length('jose')	4
length(string bytea, encoding name )	int	string在给定编码中的字符数。string必须在这个编码中有效。	length('jose', 'UTF8')	4
lpad(string text, length int [, fill text])	text	将string通过前置字符fill（默认是一个空格）填充到长度length。如果string已经长于length，则它被（从右边）截断。	lpad('hi', 5, 'xy')	xyxhi
ltrim(string text [, characters text])	text	从string的开头删除最长的只包含characters（默认是一个空格）的串	ltrim('zzzytest', 'xyz')	test
md5(string)	text	计算string的 MD5 哈希，返回十六进制的结果	md5('abc')	900150983cd24fb0 d6963f7d28e17f72
parse_ident(qualified_identifier text [, strictmode boolean DEFAULT true ] )	text[]	把qualified_identifier分成一个标识符数组， 移除单个标识符上的任何引号。默认情况下， 最后一个标识符后面的多余字符会被当做错误。但是如果第二个参数为false， 那么这一类多余的字符会被忽略（这种行为对于解析函数之类的对象名称有用）。 注意这个函数不会截断超长标识符。如果想要进行截断，可以把结果转换成name[]。	parse_ident('"SomeSchema".someTable')	{SomeSchema,sometable}
pg_client_encoding()	name	当前的客户端编码名字	pg_client_encoding()	SQL_ASCII
quote_ident(string text)	text	将给定字符串返回成合适的引用形式，使它可以在一个SQL语句字符串中被用作一个标识符。只有需要时才会加上引号（即，如果字符串包含非标识符字符或可能是大小写折叠的）。嵌入的引号会被正确地双写。	quote_ident('Foo bar')	"Foo bar"
quote_literal(string text)	text	将给定字符串返回成合适的引用形式，使它可以在一个SQL语句字符串中被用作一个字符串文字。嵌入的引号会被正确地双写。注意quote_literal对空输入返回空；如果参数可能为空，quote_nullable通常更合适。	quote_literal(E'O\'Reilly')	'O''Reilly'
quote_literal(value anyelement)	text	强迫给定值为文本并且接着将它用引号包围作为一个文本。嵌入的单引号和反斜线被正确的双写。	quote_literal(42.5)	'42.5'
quote_nullable(string text)	text	将给定字符串返回成合适的引用形式，使它可以在一个SQL语句字符串中被用作一个字符串文字；或者，如果参数为空，返回NULL。嵌入的引号会被正确地双写。	quote_nullable(NULL)	NULL
quote_nullable(value anyelement)	text	强迫给定值为文本并且接着将它用引号包围作为一个文本；或者，如果参数为空，返回NULL。嵌入的单引号和反斜线被正确的双写。	quote_nullable(42.5)	'42.5'
regexp_match(string text, pattern text [, flags text])	text[]	将从POSIX正则表达式首次匹配产生的捕获子字符串返回给string。	regexp_match('foobarbequebaz', '(bar)(beque)')	{bar,beque}
regexp_matches(string text, pattern text [, flags text])	setof text[]	将从POSIX正则表达式匹配产生的捕获子字符串返回给string。	regexp_matches('foobarbequebaz', 'ba.', 'g')	{bar} {baz} (2 rows)
regexp_replace(string text, pattern text, replacement text [, flags text])	text	替换匹配一个POSIX正则表达式的子串。	regexp_replace('Thomas', '.[mN]a.', 'M')	ThM
regexp_split_to_array(string text, pattern text [, flags text ])	text[]	使用一个POSIX正则表达式作为分隔符划分string。	regexp_split_to_array('hello world', E'\\s+')	{hello,world}
regexp_split_to_table(string text, pattern text [, flags text])	setof text	使用一个POSIX正则表达式作为分隔符划分string。	regexp_split_to_table('hello world', E'\\s+')	hello world (2 rows)
repeat(string text, number int)	text	重复string指定的number次	repeat('Pg', 4)	PgPgPgPg
replace(string text, from text, to text)	text	将string中出现的所有子串from替换为子串to	replace('abcdefabcdef', 'cd', 'XX')	abXXefabXXef
reverse(str)	text	返回反转的字符串。	reverse('abcde')	edcba
right(str text, n int)	text	返回字符串中的最后n个字符。如果n为负，返回除最前面的|n|个字符外的所有字符。	right('abcde', 2)	de
rpad(string text, length int [, fill text])	text	将string通过增加字符fill（默认为一个空格）填充到长度length。如果string已经长于length则它会被截断。	rpad('hi', 5, 'xy')	hixyx
rtrim(string text [, characters text])	text	从string的结尾删除最长的只包含characters（默认是一个空格）的串	rtrim('testxxzx', 'xyz')	test
split_part(string text, delimiter text, field int)	text	按delimiter划分string并返回给定域（从1开始计算）	split_part('abc~@~def~@~ghi', '~@~', 2)	def
strpos(string, substring)	int	指定子串的位置（和position(substring in string)相同，但是注意相反的参数顺序）	strpos('high', 'ig')	2
substr(string, from [, count])	text	提取子串（与substring(string from from for count)相同）	substr('alphabet', 3, 2)	ph
to_ascii(string text [, encoding text])	text	将string从另一个编码转换到ASCII（只支持从LATIN1、LATIN2、LATIN9和WIN1250编码的转换）	to_ascii('Karel')	Karel
to_hex(number int or bigint)	text	将number转换到它等效的十六进制表示	to_hex(2147483647)	7fffffff
translate(string text, from text, to text)	text	string中任何匹配from集合中一个字符的字符会被替换成to集合中的相应字符。如果from比to长，from中的额外字符会被删除。	translate('12345', '143', 'ax')

1. 1. 1. 1. format

函数format用于将字段的显示进行格式化。类似于C函数sprintf。语法：

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

formatstr指定了结果将被如何格式化。格式字符串中的文本，除格式说明符外，都将被直接复制到结果中。格式说明符定义了后续的函数参数将如何被格式化及插入到结果中。格式说明符由一个%字符开始，其语法表示为：

%[position][flags][width]type

其中：

1. position（可选）：形式为n&，其中n为要打印的参数的索引。如果忽略position会默认使用序列中的下一个参数；
2. flags（可选）：为符号-时，将格式说明符的输出左对齐。
3. width（可选）：用于显示格式说明符输出的最小字符数。表示形式：1）一个正整数；2）*表示使用下一个函数参数作为宽度；3）*n$的字符串表示使用第n个函数参数作为宽度。
4. type（必需）：格式转换的类型，用于产生格式说明符的输出，支持的类型为：1）s将参数格式化为一个简单的字符串；2）I将参数视为SQL标识符，并在必要时用双引号包围；3）L将参数引用为SQL文字。

下面为一些基本的格式转换的例子：

SELECT format('Hello %s', 'World');

结果：Hello World

SELECT format('Testing %s, %s, %s, %%', 'one', 'two', 'three');

结果：Testing one, two, three, %

SELECT format('INSERT INTO %I VALUES(%L)', 'Foo bar', E'O\'Reilly');

结果：INSERT INTO "Foo bar" VALUES('O''Reilly')

SELECT format('INSERT INTO %I VALUES(%L)', 'locations', E'C:\\Program Files');

结果：INSERT INTO locations VALUES(E'C:\\Program Files')

使用width和-标志的例子：

SELECT format('|%10s|', 'foo');

结果：|       foo|

SELECT format('|%-10s|', 'foo');

结果：|foo       |

SELECT format('|%*s|', 10, 'foo');

结果：|       foo|

SELECT format('|%*s|', -10, 'foo');

结果：|foo       |