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

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

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

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

说明: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

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

说明: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

说明: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=

说明: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

说明: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 删除字符串左边某一些的字符,可以时指定多个要删除的字符

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

说明: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+')= 
(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



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


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




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


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

C语言字符串操作函数 - strcpy、strcmp、strcat、反转、回文 C语言字符串操作函数 1. 字符串反转 - strRev 2. 字符串复制 - st...

常用java字符串操作函数(转) 这哥们论坛不错,转自于此。 常用String用法大全


  • 2014-04-29 15:43
  • 148KB
  • 下载


——————–数据格式化—————————-#!/usr/bin/python # -*- coding: UTF-8 -*-# 格式化字符串 str1 = "version" num = 1.0 f...