PostgreSQL 字符串操作函数[转]

转载 2015年07月09日 16:01:37

函数:string || string 
说明:String concatenation字符串连接操作
例子:'Post' || 'greSQL' = PostgreSQL

函数:string || non-string or non-string ||string
说明:String concatenation with one non-string input字符串与非字符串类型进行连接操作
例子:'Value: ' || 42 = Value: 42

函数:bit_length(string)
说明:Number of bits in string 计算字符串的位数
例子:bit_length('jose') = 32

函数:char_length(string) orcharacter_length(string)
说明:Number of characters in string 计算字符串中字符个数
例子:char_length('jose') = 4

函数:lower(string)
说明:Convert string to lower case 转换字符串为小写
例子:bit_length('jose') = 32

函数:octet_length(string)
说明:Number of bytes in string 计算字符串的字节数
例子:octet_length('jose') = 4


函数:overlay(string placing string from int [forint])
说明:Replace substring 替换字符串中任意长度的子字串为新字符串
例子:overlay('Txxxxas' placing 'hom' from 2 for 4) = 4


函数:position(substring instring)
说明:Location of specified substring 子串在一字符串中的位置
例子:position('om' in 'Thomas') = 3


函数:substring(string [from int] [forint])
说明:Extract substring 截取任意长度的子字符串
例子:substring('Thomas' from 2 for 3) = hom


函数:substring(string frompattern)
说明:Extract substring matching POSIX regular expression. See Section9.7 for more information on pattern matching.利用正则表达式对一字符串进行任意长度的字串的截取
例子:substring('Thomas' from '...$') = mas


函数:substring(string from pattern forescape)
说明:Extract substring matching SQL regular expression. See Section9.7 for more information on pattern matching.利于正则表达式对某类字符进行删除,以得到子字符串
例子:trim(both 'x' from 'xTomxx') = Tom


函数:trim([leading | trailing | both][characters] from string) 
说明:Remove the longest string containing only the characters (aspace by default) from the start/end/both ends of the string去除尽可能长开始,结束或者两边的某类字符,默认为去除空白字符,当然可以自己指定,可同时指定多个要删除的字符串
例子:trim(both 'x' from 'xTomxx') = Tom


函数:upper(string)
说明:Convert string to uppercase 将字符串转换为大写
例子:upper('tom') = TOM


函数:ascii(string)
说明:ASCII code of the first character of the argument. For UTF8returns the Unicode code point of the character. For othermultibyte encodings. the argument must be a strictly ASCIIcharacter. 得到某一个字符的Assii值
例子:ascii('x') = 120


函数:btrim(string text [, characterstext])
说明:Remove the longest string consisting only of characters incharacters (a space by default) from the start and end of string去除字符串两边的所有指定的字符,可同时指定多个字符
例子:btrim('xyxtrimyyx', 'xy') = trim


函数:chr(int)
说明:Character with the given code. For UTF8 the argument is treatedas a Unicode code point. For other multibyte encodings the argumentmust designate a strictly ASCII character. The NULL (0) characteris not allowed because text data types cannot store such bytes.得到某ACSII值对应的字符
例子:chr(65) = A


函数:convert(string bytea, src_encoding name,dest_encoding name) 
说明:Convert string todest_encoding. The original encoding is specified by src_encoding.The string must be valid in this encoding. Conversions can bedefined by CREATE CONVERSION. Also there are some predefinedconversions. See Table 9-7 for available conversions.转换字符串编码,指定源编码与目标编码
例子:convert('text_in_utf8', 'UTF8', 'LATIN1') = text_in_utf8represented in ISO 8859-1 encoding


函数:convert_from(string bytea, src_encodingname) 
说明:Convert string to the databaseencoding. The original encoding is specified by src_encoding. Thestring must be valid in this encoding.转换字符串编码,自己要指定源编码,目标编码默认为数据库指定编码,
例子:convert_from('text_in_utf8', 'UTF8') = text_in_utf8 representedin the current database encoding


函数:convert_to(string text, dest_encodingname) 
说明:Convert string todest_encoding.转换字符串编码,源编码默认为数据库指定编码,自己要指定目标编码,
例子:convert_to('some text', 'UTF8') = some text represented in theUTF8 encoding


函数:decode(string text, typetext) 
说明:Decode binary data from stringpreviously encoded with encode. Parameter type is same as inencode. 对字符串按指定的类型进行解码
例子:decode('MTIzAAE=', 'base64') = 123\000\001


函数:encode(data bytea, typetext) 
说明:Encode binary data todifferent representation. Supported types are: base64, hex, escape.Escape merely outputs null bytes as \000 and doubles backslashes.与decode相反,对字符串按指定类型进行编码
例子:encode(E'123\\000\\001', 'base64') = MTIzAAE=


函数:initcap(string)
说明:Convert the first letter ofeach word to uppercase and the rest to lowercase. Words aresequences of alphanumeric characters separated by non-alphanumericcharacters. 将字符串所有的单词进行格式化,首字母大写,其它为小写
例子:initcap('hi THOMAS') = Hi Thomas


函数:length(string)
说明:Number of characters in string讲算字符串长度
例子:length('jose') = 4


函数:length(stringbytea, encoding name)
说明:Number ofcharacters in string in the given encoding. The string must bevalid in this encoding. 计算字符串长度,指定字符串使用的编码
例子:length('jose', 'UTF8') = 4


函数:lpad(string text, length int [, filltext]) 
说明:Fill up the string to lengthlength by prepending the characters fill (a space by default). Ifthe string is already longer than length then it is truncated (onthe right).对字符串左边进行某类字符自动填充,即不足某一长度,则在左边自动补上指定的字符串,直至达到指定长度,可同时指定多个自动填充的字符
例子:lpad('hi', 5, 'xy') = xyxhi


函数:ltrim(string text [, characterstext]) 
说明:Remove the longest stringcontaining only characters from characters (a space by default)from the start of string 删除字符串左边某一些的字符,可以时指定多个要删除的字符
例子:trim


函数:md5(string)
说明:Calculates the MD5 hash ofstring, returning the result in hexadecimal 将字符串进行md5编码
例子:md5('abc') = 900150983cd24fb0 d6963f7d28e17f72


函数:pg_client_encoding()
说明:Current client encoding name得到pg客户端编码
例子:pg_client_encoding() = SQL_ASCII


函数:quote_ident(string text)
说明:Return the given stringsuitably quoted to be used as an identifier in an SQL statementstring. Quotes are added only if necessary (i.e., if the stringcontains non-identifier characters or would be case-folded).Embedded quotes are properly doubled. 对某一字符串加上两引号
例子:quote_ident('Foo bar') = "Foo bar"


函数:quote_literal(string text)
说明:Return the given stringsuitably quoted to be used as a string literal in an SQL statementstring. Embedded single-quotes and backslashes are properlydoubled. 对字符串里两边加上单引号,如果字符串里面出现sql编码的单个单引号,则会被表达成两个单引号
例子:quote_literal('O\'Reilly') = 'O''Reilly'


函数:quote_literal(value anyelement)
说明:Coerce the given value to textand then quote it as a literal. Embedded single-quotes andbackslashes are properly doubled.将一数值转换为字符串,并为其两边加上单引号,如果数值中间出现了单引号,也会被表示成两个单引号
例子:quote_literal(42.5) = '42.5'


函数:regexp_matches(string text, pattern text [,flags text])
说明:Returnall captured substrings resulting from matching a POSIX regularexpression against the string. See Section 9.7.3 for moreinformation. 对字符串按正则表达式进行匹配,如果存在则会在结果数组中表示出来
例子:regexp_matches('foobarbequebaz', '(bar)(beque)') ={bar,beque}


函数:regexp_replace(string text, pattern text,replacement text [, flags text])
说明:Replace substring(s) matchinga POSIX regular expression. See Section 9.7.3 for more information.利用正则表达式对字符串进行替换
例子:regexp_replace('Thomas', '.[mN]a.', 'M') = ThM


函数:regexp_split_to_array(string text, patterntext [, flags text ])
说明:Split string using a POSIXregular expression as the delimiter. See Section 9.7.3 for moreinformation. 利用正则表达式将字符串分割成数组
例子:regexp_split_to_array('hello world', E'\\s+') ={hello,world}


函数:regexp_split_to_table(string text, patterntext [, flags text])
说明:Split string using a POSIXregular expression as the delimiter. See Section 9.7.3 for moreinformation. 利用正则表达式将字符串分割成表格
例子:regexp_split_to_table('hello world', E'\\s+')= 
hello
world
(2 rows)


函数:repeat(string text, number int)
说明:Repeat string the specifiednumber of times 重复字符串一指定次数
例子:repeat('Pg', 4) = PgPgPgPg


函数:replace(string text, from text, totext)
说明:Replace alloccurrences in string of substring from with substring to将字符的某一子串替换成另一子串
例子:('abcdefabcdef', 'cd', 'XX') = abXXefabXXef


函数:rpad(string text, length int [, filltext]) 
说明:Fill up the string to lengthlength by appending the characters fill (a space by default). Ifthe string is already longer than length then it is truncated.对字符串进行填充,填充内容为指定的字符串
例子:rpad('hi', 5, 'xy') = hixyx


函数:rtrim(string text [, characterstext])
说明:Remove thelongest string containing only characters from characters (a spaceby default) from the end of string 
去除字符串右边指定的字符
例子:rtrim('trimxxxx', 'x') = trim


函数:split_part(string text, delimiter text,field int)
说明:Splitstring on delimiter and return the given field (counting from one)对字符串按指定子串进行分割,并返回指定的数值位置的值
例子:split_part('abc~@~def~@~ghi', '~@~', 2) = def


函数:strpos(string, substring)
说明:Location of specifiedsubstring (same as position(substring in string), but note thereversed argument order) 指定字符串在目标字符串的位置
例子:strpos('high', 'ig') = 2


函数:substr(string, from [, count])
说明:Extract substring (same assubstring(string from from for count)) 截取子串
例子:substr('alphabet', 3, 2) = ph


函数:to_ascii(string text [, encodingtext])
说明:Convert stringto ASCII from another encoding (only supports conversion fromLATIN1, LATIN2, LATIN9, and WIN1250 encodings)将字符串转换成ascii编码字符串
例子:to_ascii('Karel') = Karel


函数:to_hex(number int or bigint)
说明:Convert number to itsequivalent hexadecimal representation  对数值进行十六进制编码
例子:to_hex(2147483647) = 7fffffff


函数:translate(string text, from text, totext) 
说明:Any character in string thatmatches a character in the from set is replaced by thecorresponding character in the to set将字符串中某些匹配的字符替换成指定字符串,目标字符与源字符都可以同时指定多个
例子:translate('12345', '14', 'ax') = a23x5

三天入门Python---基本语法(第一天)

三天入门Python:第一天Python基本语法

tushare获取破新高的股票

原帖: http://www.30daydo.com/article/70 股市有句话,新高后有新高。 因为新高后说明消化了前面的套牢盘。 所以这个时候的阻力很小。   下面使用一个例子来用代码获...
  • yagamil
  • yagamil
  • 2016年07月29日 23:35
  • 4919

Postgresql数据库的一些字符串操作函数

http://www.cnblogs.com/wuhenke/archive/2010/08/02/1790750.html 函数:regexp_split_to_table(string te...

Postgresql数据库的一些字符串操作函数

现把Postgresql的字符串操作函数罗列在以,以便日方使用函数:string || string 说明:String concatenation 字符串连接操作例子:Post || greSQL ...

Postgresql的字符串操作函数

现把Postgresql的字符串操作函数罗列在以,以便日方使用 函数:string || string  说明:String concatenation 字符串连接操作 例子:'Post' ...

Postgresql数据库的一些字符串操作函数

最近在使用Postgresql数据库,遇到一些对查询结果的处理,不想在后台再遍历一遍,就找了一下关于字符串的函数,于是发现了这些: 函数:string || string  说明:...

【数据库】postgresql小函数---将字符串split成为数组

declare _strres varchar(800); declare _originStr varchar(100); declare _cindex INTEGER; declare _del...
  • cdnight
  • cdnight
  • 2014年01月09日 18:00
  • 6753

memo:PostgreSQL字符串函数

字符串函数和运算符是我们经常在SQL需要用到的。尤其是regexp_split_to_array和regexp_split_to_table(string text, pattern text [,...

PostgreSQL字符串处理函数

前期准备,新建数据表 CREATE TABLE mytable (   id serial NOT NULL,   "name" character varying(20),   geom geome...
  • xh16319
  • xh16319
  • 2013年09月07日 18:22
  • 3275

postgresql 数据类型转换,日期操作函数

各种数据类型(日期/时间、integer、floating point和numeric)转换成格式化的字符串以及反过来从格式化的字符串转换成指定的数据类型。下面列出了这些函数,它们都遵循一个公共的...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:PostgreSQL 字符串操作函数[转]
举报原因:
原因补充:

(最多只允许输入30个字)