1.數(shù)據(jù)修復(fù)最先考慮通過(guò)db內(nèi)做修復(fù)，實(shí)在不行，在考慮外部應(yīng)用程序通過(guò)jdbc修復(fù).

比如一個(gè)場(chǎng)景：profile_image_url與enlarge_image_url都是微博用戶信息返回的字段. 前者是http://tp2.sinaimg.cn/1928431341/50/5621497131/1，后者正常情況是http: //tp2.sinaimg.cn/1928431341/180/5621497131/1, 此時(shí)如果修復(fù)后者的數(shù)據(jù)，只需將/50/替換成/180/，只需通過(guò)postgres的字符函數(shù)解決。

?

2.常用函數(shù)

2.1常用字符串函數(shù)列表

注意, 下頁(yè)的示例中字符串都是可以用表中的字段替代.? 測(cè)試函數(shù)可以類(lèi)似 select "char_length"('string')；? *標(biāo)識(shí)不常用, 字符串在任何庫(kù)的函數(shù)最主要的不過(guò)就是substring, position, length, replace幾種，類(lèi)似于db的CRUD。

?

函數(shù)：string || string　

說(shuō)明：String concatenation 字符串連接操作
例子：'Post' || 'greSQL'　＝　PostgreSQL

?

函數(shù)：string || non-string or non-string || string
說(shuō)明：String concatenation with one non-string input 字符串與非字符串類(lèi)型進(jìn)行連接操作
例子：'Value: ' || 42 = Value: 42

?

函數(shù)：bit_length(string)
說(shuō)明：Number of bits in string 計(jì)算字符串的位數(shù)
例子：bit_length('jose') = 32

?

函數(shù)：char_length(string) or character_length(string)
說(shuō)明：Number of characters in string 計(jì)算字符串中字符個(gè)數(shù)
例子：char_length('jose') = 4

與length一樣

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

函數(shù)：lower(string)
說(shuō)明：Convert string to lower case 轉(zhuǎn)換字符串為小寫(xiě)
例子：select "lower"('ABC') =abc

?

函數(shù)：octet_length(string)
說(shuō)明：Number of bytes in string 計(jì)算字符串的字節(jié)數(shù)
例子：octet_length('jose') = 4

函數(shù)：overlay(string placing string from int [for int])
說(shuō)明：Replace substring 替換字符串中任意長(zhǎng)度的子字串為新字符串
例子：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='';

函數(shù)：position(substring in string)
說(shuō)明：Location of specified substring 子串在一字符串中的位置
例子：position('om' in 'Thomas') = 3

函數(shù)：substring(string [from int] [for int])
說(shuō)明：Extract substring 截取任意長(zhǎng)度的子字符串
例子：substring('Thomas' from 2 for 3) = hom

函數(shù)：substring(string from pattern)
說(shuō)明：Extract substring matching POSIX regular expression. See Section 9.7 for more information on pattern matching. 利用正則表達(dá)式對(duì)一字符串進(jìn)行任意長(zhǎng)度的字串的截取
例子：substring('Thomas' from '...$') = mas

函數(shù)：substring(string from pattern for escape)
說(shuō) 明：Extract substring matching SQL regular expression. See Section 9.7 for more information on pattern matching. 利于正則表達(dá)式對(duì)某類(lèi)字符進(jìn)行刪除，以得到子字符串
例子：trim(both 'x' from 'xTomxx') = Tom

函數(shù)：trim([leading | trailing | both] [characters] from string)
說(shuō) 明：Remove the longest string containing only the characters (a space by default) from the start/end/both ends of the string 去除盡可能長(zhǎng)開(kāi)始，結(jié)束或者兩邊的某類(lèi)字符，默認(rèn)為去除空白字符，當(dāng)然可以自己指定，可同時(shí)指定多個(gè)要?jiǎng)h除的字符串
例子：trim(both 'x' from 'xTomxx') = Tom

函數(shù)：upper(string)
說(shuō)明：Convert string to uppercase 將字符串轉(zhuǎn)換為大寫(xiě)
例子：upper('tom') = TOM

函數(shù)：ascii(string)
說(shuō)明：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. 得到某一個(gè)字符的Assii值
例子：ascii('x') = 120

函數(shù)：btrim(string text [, characters text])
說(shuō) 明：Remove the longest string consisting only of characters in characters (a space by default) from the start and end of string 去除字符串兩邊的所有指定的字符，可同時(shí)指定多個(gè)字符
例子：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 ' %'

?