資料修復優先在資料庫層完成 (SQL/函數)，不可行再考慮外部程式。本文彙整 PostgreSQL 核心字串函數，並以「將微博頭像 URL 中 /50/ 替換為 /180/」實戰案例串聯常用函數。
-- 原始：https://tp2.sinaimg.cn/1928431341/50/5621497131/1
-- 目標：https://tp2.sinaimg.cn/1928431341/180/5621497131/1
-- 方法 1：overlay + position
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, 1, position('/50/' IN profile_image_url) - 1)
|| '/180/'
|| substring(profile_image_url, position('/50/' IN profile_image_url) + 4)
WHERE enlarge_image_url = '';
-- 方法 3：replace (最簡潔)
UPDATE t_sns_member
SET enlarge_image_url = replace(profile_image_url, '/50/', '/180/')
WHERE enlarge_image_url = '';| 函數 | 用途 | 範例 |
|---|---|---|
|| | 字串串接 | 'A' || 'B' = 'AB' |
length(str) / char_length(str) | 字元長度 | length('abc') = 3 |
octet_length(str) | 位元組長度 | octet_length('中') = 3 (UTF8) |
substring(str FROM start FOR len) | 擷取子字串 | substring('abcdef' FROM 2 FOR 3) = 'bcd' |
position(sub IN str) | 子字串位置 (1-based) | position('cd' IN 'abcdef') = 3 |
strpos(str, sub) | 同 position，參數順序相反 | strpos('abcdef', 'cd') = 3 |
replace(str, from, to) | 全部替換 | replace('a_b_c', '_', '-') = 'a-b-c' |
overlay(str PLACING new FROM start FOR len) | 指定位置替換 | overlay('abcdef' PLACING 'XY' FROM 2 FOR 2) = 'aXYef' |
split_part(str, delim, n) | 分割取第 n 段 | split_part('a,b,c', ',', 2) = 'b' |
trim([BOTH|LEADING|TRAILING] chars FROM str) | 去除首尾字元 | trim(BOTH 'x' FROM 'xxaxx') = 'a' |
upper(str) / lower(str) | 大小寫轉換 | upper('abc') = 'ABC' |
initcap(str) | 每單字首字大寫 | initcap('hello world') = 'Hello World' |
regexp_replace(str, pattern, repl) | 正則替換 | regexp_replace('a1b2', '\d', '') = 'ab' |
md5(str) | MD5 雜湊 | md5('abc') = '900150983cd24fb0d6963f7d28e17f72' |
encode(data, 'base64') / decode(str, 'base64') | Base64 編解碼 | encode('abc', 'base64') = 'YWJj' |
to_hex(num) | 數值轉十六進位 | to_hex(255) = 'ff' |
CURRENT_DATE, CURRENT_TIMESTAMP, NOW()age(timestamp)：計算年齡/間隔。date_trunc('month', ts)：截斷至月/日/時。EXTRACT(field FROM ts)：取得年/月/日/時/分/秒/epoch。ts + interval '3 months'：日期運算 (標準 SQL)。convert(str, src_encoding, dest_encoding) -- 指定來源/目標編碼
convert_from(str, src_encoding) -- 轉為資料庫編碼
convert_to(str, dest_encoding) -- 從資料庫編碼轉出
