函数postgres（一）

1.数据修复最先考虑通过db内做修复，实在不行，在考虑外部应用程序通过jdbc修复.

比如一个场景：profile_image_url与enlarge_image_url都是微博用户信息返回的字段. 前者是http://tp2.sinaimg.cn/1928431341/50/5621497131/1，后者正常情况是http: //tp2.sinaimg.cn/1928431341/180/5621497131/1, 此时如果修复后者的数据，只需将/50/替换成/180/，只需通过postgres的字符函数解决。

 

2.常用函数

2.1常用字符串函数列表

注意, 下页的示例中字符串都是可以用表中的字段替代.  测试函数可以类似 select "char_length"('string')；  *标识不常用, 字符串在任何库的函数最主要的不过就是substring, position, length, replace几种，类似于db的CRUD。

 

函数：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) or character_length(string)
说明：Number of characters in string 计算字符串中字符个数
例子：char_length('jose') = 4

与length一样

select "char_length"('string'),"length"('string');  res: 6 6

函数：lower(string)
说明：Convert string to lower case 转换字符串为小写
例子：select "lower"('ABC') =abc

 

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

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

又比如要将'http://tp2.sinaimg.cn/1928431341/50/5621497131/1'中的/50/替换成/180/,可以使用的方法：

1.

update t_sns_member  set  enlarge_image_url= overlay(profile_image_url placing '/180/' from position('/50/' in profile_image_url) for 4)  where enlarge_image_url=''

2.不使用替换，substring+position+||去拼新串

update t_sns_member  set  enlarge_image_url=substring(profile_image_url,0,position('/50/' in profile_image_url))||'/180/'||substring(profile_image_url,position('/50/' in profile_image_url)+4,char_length(profile_image_url)) where enlarge_image_url='';

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

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

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

函数：substring(string from pattern for escape)
说 明：Extract substring matching SQL regular expression. See Section 9.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 (a space 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 UTF8 returns the Unicode code point of the character. For other multibyte encodings. the argument must be a strictly ASCII character. 得到某一个字符的Assii值
例子：ascii('x') = 120

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

 

update property set memorial_no = btrim(memorial_no, ' ') where memorial_no like ' %'

或update property set memorial_no = trim(both ' ' from memorial_no) where memorial_no like ' %'