Chương 9. Hàm và toán tử
9.4. Hàm và toán tử chuỗi
Phần này mô tả các hàm và toán tử cho việc kiểm tra và điều khiển các giá trị chuỗi. Các chuỗi trong ngữ cảnh này bao gồm các giá trị các dạng character, character varying, và text. Trừ phi được lưu ý khác đi, tất cả các hàm được liệt kê bên dưới làm việc trong tất cả các dạng, nhưng là khác nhau về các hiệu ứng tiềm năng của việc chêm vào chỗ trống một cách tự động khi sử dụng dạng
character. Một số hàm cũng tồn tại bẩm sinh đối với các dạng chuỗi bit.
SQL xác định một số hàm chuỗi mà sử dụng các từ khóa, thay vì các dấu phẩy, để tách các đối số.
Các chi tiết ở trong Bảng 9-5. PostgreSQL cũng đưa ra các phiên bản các hàm mà sử dụng cú pháp gọi hàm thông thường (xem Bảng 9-6).
Lưu ý: Trước PostgreSQL v8.3, các hàm đó có thể cũng âm thầm chấp nhận các giá trị của vài dạng dữ liệu không phải là chuỗi, vì sự hiện diện của sự ép buộc ngấm ngầm từ các dạng dữ liệu đó đối với text. Những ép buộc đó từng được loại bỏ vì chúng thường xuyên gây ra các hành vi gây ngạc nhiên. Tuy nhiên, toán tử ghép chuỗi (||) vẫn chấp nhận đầu vào không phải là chuỗi, nên miễn là ít nhất một đầu vào là ở dạng chuỗi, như được nêu trong Bảng 9- 5. Đối với các trường hợp khác, hãy chèn một sự cưỡng bức rõ ràng vào text nếubạn cần đúp bản hành vi trước đó.
Bảng 9-5. Hàm và toán tử chuỗi SQL
Hàm Dạng trả về Mô tả Ví dụ Kết quả
string || string text Ghép chuỗi ’Post’ || ’greSQL’ PostgreSQL
String || non-string or non-string || string
text Ghép chuỗi với một đầu vào không phải là chuỗi
’Value: ’ || 42 Value: 42 bit_length(string) int Số bit trong chuỗi bit_length(’jose3’2)
char_length(string) hoặc
character_length(string) int Số ký tự trong chuỗi char_length(’jos4e’)
lower(string) text Chuyển chuỗi sang chữ thường lower(’TOM’) tom octet_length(string) int Số byte trong chuỗi octet_length(’jo4se’)
overlay(string placing string from int [for int])
text Thay chuỗi con overlay(’Txxxxas’
đặt ’hom’ từ 2 với 4)
Thomas
Hàm Dạng trả về Mô tả Ví dụ Kết quả position(substring in
string)
int Ví trí chuỗi con được chỉ định position(’om’ trong
’Thomas’)
3 substring(string [from int]
[for int])
text Trích ra chuỗi con substring(’Thomas’
từ 2 với 3)
hom substring(string from
pattern)
text Trích chuỗi con khớp với biểu thức POSIX thông thường. Xem Phần 9.7 để có thêm thông tin về khớp mẫu.
substring(’Thomas’ từ ’...
$’)
mas
substring(string from
pattern for escape) text Trích chuỗi con khớp với biểu thức SQL thông thường. Xem Phần 9.7 để có thêm thông tin về khớp mẫu.
substring(’Thomas’
từ ’%#"o_a#"_’ với ’#’) oma
trim([leading | trailing | both] [characters] from string)
text Loại bỏ chuỗi dài nhất có chứa chỉ các ký tự (một dấu trống mặc định) từ điểm đầu/cuối/2 đầu của chuỗi
trim(both ’x’ từ
’xTomxx’) Tom
upper(string) text Chuyển chuỗi thành chữ hoa upper(’tom’) TOM
Các hàm bổ sung để điều khiển chuỗi là sẵn có và được liệt kê trong Bảng 9-6. Một số chúng được sử dụng nội bộ để triển khai các hàm chuỗi theo tiêu chuẩn SQL được liệt kê trong Bảng 9-5.
Bảng 9-6. Các hàm chuỗi khác
Hàm Dạng trả về Mô tả Ví dụ Kết quả
ascii(string) int Mã ASCII ký tự đầu tiên của đối số. UTF8 trả về điểm mã ký tự Unicode.
Những mã hóa nhiều byte khác thì đối số phải là một ký tự ASCII.
ascii(’x’) 120
btrim(string text [,
characters text]) text Loại bỏ chuỗi dài nhất chỉ bao gồm các ký tự trong các ký tự (một dấu trống là mặc định) từ đầu và cuối của chuỗi.
btrim(’xyxtrimyyx’,
’xy’) trim
chr(int) text Ký tự với mã được đưa ra.
Với UTF8 thì đối số được theo dõi như một điểm mã Unicode, đối với các mã hóa nhiều byte khác thì đối số phải chỉ định một ký tự ASCII. Ký tự NULL (0) là không được phép vì các dạng dữ liệu văn bản không thể lưu trữ các byte như vậy.
chr(65) A
convert(string bytea, src_encoding name, dest_encoding name)
bytea Biến đối chuỗi sang dest_encoding. Việc mã hóa ban đầu được src_encoding
convert(’text_in_utf8’,
’UTF8’, ’LATIN1’) text_in_utf8 represented in Latin-1 encoding
Hàm Dạng trả về Mô tả Ví dụ Kết quả chỉ định. Chuỗi đó phải là
hợp lệ trong việc mã hóa này. Các biến đổi có thể do CREATE CONVERSION xác định. Hơn nữa sẽ có một số biến đổi được xác định trước. Xem Bảng 9-7 cho các biến đổi có sẵn.
(ISO 8859-1)
convert_from(string bytea, src_encoding name)
text Biến đổi chuỗi sang mã cơ sở dữ liệu. Mã gốc ban đầu được src_encoding chỉ định. Chuỗi đó phải hợp lệ trong mã này.
convert_from(’text_in_
utf8’, ’UTF8’)
text_in_utf8 represented in the current database encoding convert_to(string text,
dest_encoding name)
bytea Biến đổi chuỗi sang dest_encoding.
convert_to(’some text’,
’UTF8’)
some text
represented in the UTF8 encoding decode(string text, format
text) bytea Giải mã các dữ liệu nhị phân từ trình diễn văn bản trong chuỗi. Các lựa chọn cho định dạng là y hệt như trong mã hóa.
decode(’MTIzAAE=’,
’base64’) x3132330001
encode(data bytea, format
text) text Mã hóa dữ liệu nhị phân thành một trình diễn văn bản. Các định dạng được hỗ trợ gồm: base64, hex, escape. escape biến đổi các byte 0 và các byte trong thiết lập bit cao sang các tuần tự cơ số 8 (\nnn) và đúp bản các dấu chéo ngược.
encode(E’123\\000\\001
’, ’base64’) MTIzAAE=
initcap(string) text Biến đổi ký tự đầu của từng từ thành chữ hoa và phần còn lại thành chữ thường.
Các từ là tuần tự của các ký tự abc được tách bạch nhau bằng các ký tự không phải abc.
initcap(’hi THOMAS’) Hi Thomas
length(string) int Số lượng các ký tự trong
chuỗi length(’jose’) 4
length(stringbytea,
encoding name ) int Số lượng các ký tự trong chuỗi trong mã được đưa ra. Chuỗi đó phải là hợp lệ trong mã này.
length(’jose’, ’UTF8’) 4
lpad(string text, length int [, fill text])
text Điền chuỗi vào độ dài bằng việc treo trước ký tự điền (mặc định một dấu trắng).
Nếu chuỗi đó dài hơn rồi so với độ dài thì nó bị cắt bớt (ở bên phải).
lpad(’hi’, 5, ’xy’) xyxhi
Hàm Dạng trả về Mô tả Ví dụ Kết quả ltrim(string text [,
characters text])
text Loại bỏ chuỗi dài nhất chỉ chứa các ký tự từ các ký tự (mặc định một dấu trắng) từ đầu chuỗi.
ltrim(’zzzytrim’, ’xyz’) trim
md5(string) text Tính hàm băm MD5 của chuỗi, trả về kết quả dạng cơ số 16
md5(’abc’) 900150983cd24fb0
d6963f7d28e17f72 pg_client_encoding() name Tên mã hóa trạm hiện hành. pg_client_encoding SQL()ASCII quote_ident(string text) text Trả về chuỗi được đưa ra
được đưa vào dấu ngoặc kép phù hợp để sử dụng như một mã định danh trong một chuỗi lệnh SQL.
Các dấu ngoặc kép được bổ sung chỉ nếu cần thiết (như, nếu chuỗi đó gồm các ký tự không phải của mã định danh hoặc có thể là chữ gộp). Các dấu ngoặc kép được nhúng được đúp bản phù hợp. Xem thêm Ví dụ 39-1.
quote_ident(’Foo bar’) "Foo bar"
quote_literal(string
text) text Trả về chuỗi được đưa ra phù hợp được đưa vào các dấu ngoặc kép để sử dụng như một hằng chuỗi trong một chuỗi lệnh SQL. Các dấu ngoặc kép đơn được nhúng và các dấu chéo ngược được đúp bản phù hợp. Lưu ý quote_literal trả về null ở đầu vào null; nếu đối số có thể là null, quote_nullable thường phù hợp hơn. Xem thêm Ví dụ 39-1.
quote_literal(E’O\”’RR eeiilllyy’’)
quote_literal(value
anyelement) text Ép giá trị được đưa ra thành văn bản và sau đó đưa nó vào dấu ngoặc kép như một hằng. Các dấu ngoặc kép đơn và các dấu chéo ngược được đúp bản phù hợp.
quote_literal(42’.452).5
’
quote_nullable(string
text) text Trả về chuỗi được đưa ra được đưa vào các dấu ngoặc kép phù hợp để sử dụng như một hằng chuỗi trong một chuỗi lệnh SQL hoặc, nếu đối số là null, thì trả về NULL. Các dấu ngoặc đơn được nhúng và các dấu chéo ngược được đúp bản phù hợp. Xem
quote_nullable(NULL)
Hàm Dạng trả về Mô tả Ví dụ Kết quả thêm Ví dụ 39-1.
quote_nullable(value
anyelement) text Ép giá trị được đưa ra thành văn bản và sau đó đưa nó vào các dấu ngoặc kép như một hằng; hoặc, nếu đối số là null, thì trả về NULL.
Các dấu ngoặc kép được nhúng và các dấu chéo ngược được đúp bản phù hợp.
quote_nullable(4’24.25.
)5’
regexp_matches(string text, pattern text [, flags text]) setof text[]
Trả về tất cả các chuỗi con bắt được có được từ việc trùng với một biểu thức POSIX thông thường đối với chuỗi đó. Xem phần 9.7.3 để có thêm thông tin.
regexp_matches(’foobar bequebaz’, ’(bar) (beque)’) {bar,beque}
regexp_replace(string text, pattern text, replacement text [, flags text])
text Trả về (các) chuỗi con khớp với một biểu thức POSIX thông thường. Xem Phần 9.7.3 để có thêm thông tin.
regexp_replace(’Thoma s’, ’.[mN]a.’, ’M’) ThM
regexp_split_to_array(stri ng text, pattern text [, flags text ]) text[]
Chia chuỗi bằng việc sử dụng một biểu thức POSIX thông thường như là dấu tách. Xem Phần 9.7.3 để có thêm thông tin.
regexp_split_to_array(’
hello world’, E’\\s+’)
{hello,world}
regexp_split_to_table(stri ng text, pattern text [, flags text]) setof text
Chia chuỗi bằng việc sử dụng một biểu thức POSIX thông thường như là dấu tách. Xem Phần 9.7.3 để có thêm thông tin.
regexp_split_to_table(’
hello world’, E’\\s+’) Helloworld (2 rows)
repeat(string text, number
int) text Lặp lại chuỗi với số lần
được chỉ định. repeat(’Pg’, 4) PgPgPgPg
replace(string text, from
text, to text) text Thay thế tất cả các trường hợp trong chuỗi của chuỗi con từ bằng chuỗi con tới
replace(’abcdefabcdef’,
’cd’, ’XX’) abXXefabXXef
rpad(string text, length int [, fill text])
text Điền chuỗi vào độ dài bằng việc nối thêm các ký tự điền (mặc định một ký tự trắng). Nếu chuỗi đó dài hơn rồi so với chiều dài đó thì nó sẽ bị cắt bớt.
rpad(’hi’, 5, ’xy’) hixyx
rtrim(string text [,
characters text]) text Loại bỏ chuỗi dài nhất chỉ chứa các ký tự từ các ký tự (mặc định là một dấu trắng) từ cuối của chuỗi.
rtrim(’trimxxxx’, ’x’) trim
split_part(string text, delimiter text, field int)
text Chia chuỗi theo dấu phân cách và trả về trường được đưa ra (tính từ 1).
split_part(’abc~@~def~
@~ghi’, ’~@~’, 2)
def
strpos(string, substring) int Địa điểm của chuỗi con
được chỉ định (y hệt như vị strpos(’high’, ’ig’) 2
Hàm Dạng trả về Mô tả Ví dụ Kết quả trí (chuỗi con trong chuỗi),
như lưu ý trật tự ngược của đối số)
substr(string, from [,
count]) text Trích chuỗi con (y hệt như
chuỗi con (chuỗi từ đó để đếm) substr(’alphabet’, 3, 2) ph to_ascii(string text [, encoding text]) text. Biến đổi chuỗi sang ASCII từ việc mã hóa khác (chỉ hỗ trợ biến đổi từ các mã hóa
LATIN1, LATIN2,
LATIN9, và WIN1250)
to_ascii(’Karel’K)arel
to_hex(number int or bigint)
text Biến đổi số thành các trình bày tương đương của nó ở cơ số 16
to_hex(21474836477f) ffffff
translate(string text, from text, to text)
text Bất kỳ ký tự nào dạng chuỗi mà khớp một ký tự bên trong tập hợp từ được thay thế bằng ký tự tương ứng trong tập hợp đến.
translate(’12345’, ’14’,
’ax’) a23x5
Xem thêm hàm tổng hợp string_agg ở Phần 9.18.
Bảng 9-7. Các chuyển đổi được xây dựng sẵn
Tên chuyển đổi a Mã nguồn Mã đích
ascii_to_mic SQL_ASCII MULE_INTERNAL
ascii_to_utf8 SQL_ASCII UTF8
big5_to_euc_tw BIG5 EUC_TW
big5_to_mic BIG5 MULE_INTERNAL
big5_to_utf8 BIG5 UTF8
euc_cn_to_mic EUC_CN MULE_INTERNAL
euc_cn_to_utf8 EUC_CN UTF8
euc_jp_to_mic EUC_JP MULE_INTERNAL
euc_jp_to_sjis EUC_JP SJIS
euc_jp_to_utf8 EUC_JP UTF8
euc_kr_to_mic EUC_KR MULE_INTERNAL
euc_kr_to_utf8 EUC_KR UTF8
euc_tw_to_big5 EUC_TW BIG5
euc_tw_to_mic EUC_TW MULE_INTERNAL
euc_tw_to_utf8 EUC_TW UTF8
gb18030_to_utf8 GB18030 UTF8
gbk_to_utf8 GBK UTF8
Tên chuyển đổi a Mã nguồn Mã đích
iso_8859_10_to_utf8 LATIN6 UTF8
iso_8859_13_to_utf8 LATIN7 UTF8
iso_8859_14_to_utf8 LATIN8 UTF8
iso_8859_15_to_utf8 LATIN9 UTF8
iso_8859_16_to_utf8 LATIN10 UTF8
iso_8859_1_to_mic LATIN1 MULE_INTERNAL
iso_8859_1_to_utf8 LATIN1 UTF8
iso_8859_2_to_mic LATIN2 MULE_INTERNAL
iso_8859_2_to_utf8 LATIN2 UTF8
iso_8859_2_to_windows_1250 LATIN2 WIN1250
iso_8859_3_to_mic LATIN3 MULE_INTERNAL
iso_8859_3_to_utf8 LATIN3 UTF8
iso_8859_4_to_mic LATIN4 MULE_INTERNAL
iso_8859_4_to_utf8 LATIN4 UTF8
iso_8859_5_to_koi8_r ISO_8859_5 KOI8R
iso_8859_5_to_mic ISO_8859_5 MULE_INTERNAL
iso_8859_5_to_utf8 ISO_8859_5 UTF8
iso_8859_5_to_windows_1251 ISO_8859_5 WIN1251
iso_8859_5_to_windows_866 ISO_8859_5 WIN866
iso_8859_6_to_utf8 ISO_8859_6 UTF8
iso_8859_7_to_utf8 ISO_8859_7 UTF8
iso_8859_8_to_utf8 ISO_8859_8 UTF8
iso_8859_9_to_utf8 LATIN5 UTF8
johab_to_utf8 JOHAB UTF8
koi8_r_to_iso_8859_5 KOI8R ISO_8859_5
koi8_r_to_mic KOI8R MULE_INTERNAL
koi8_r_to_utf8 KOI8R UTF8
koi8_r_to_windows_1251 KOI8R WIN1251
koi8_r_to_windows_866 KOI8R WIN866
koi8_u_to_utf8 KOI8U UTF8
mic_to_ascii MULE_INTERNAL SQL_ASCII
mic_to_big5 MULE_INTERNAL BIG5
mic_to_euc_cn MULE_INTERNAL EUC_CN
mic_to_euc_jp MULE_INTERNAL EUC_JP
mic_to_euc_kr MULE_INTERNAL EUC_KR
mic_to_euc_tw MULE_INTERNAL EUC_TW
mic_to_iso_8859_1 MULE_INTERNAL LATIN1
Tên chuyển đổi a Mã nguồn Mã đích
mic_to_iso_8859_2 MULE_INTERNAL LATIN2
mic_to_iso_8859_3 MULE_INTERNAL LATIN3
mic_to_iso_8859_4 MULE_INTERNAL LATIN4
mic_to_iso_8859_5 MULE_INTERNAL ISO_8859_5
mic_to_koi8_r MULE_INTERNAL KOI8R
mic_to_sjis MULE_INTERNAL SJIS
mic_to_windows_1250 MULE_INTERNAL WIN1250
mic_to_windows_1251 MULE_INTERNAL WIN1251
mic_to_windows_866 MULE_INTERNAL WIN866
sjis_to_euc_jp SJIS EUC_JP
sjis_to_mic SJIS MULE_INTERNAL
sjis_to_utf8 SJIS UTF8
tcvn_to_utf8 WIN1258 UTF8
uhc_to_utf8 UHC UTF8
utf8_to_ascii UTF8 SQL_ASCII
utf8_to_big5 UTF8 BIG5
utf8_to_euc_cn UTF8 EUC_CN
utf8_to_euc_jp UTF8 EUC_JP
utf8_to_euc_kr UTF8 EUC_KR
utf8_to_euc_tw UTF8 EUC_TW
utf8_to_gb18030 UTF8 GB18030
utf8_to_gbk UTF8 GBK
utf8_to_iso_8859_1 UTF8 LATIN1
utf8_to_iso_8859_10 UTF8 LATIN6
utf8_to_iso_8859_13 UTF8 LATIN7
utf8_to_iso_8859_14 UTF8 LATIN8
utf8_to_iso_8859_15 UTF8 LATIN9
utf8_to_iso_8859_16 UTF8 LATIN10
utf8_to_iso_8859_2 UTF8 LATIN2
utf8_to_iso_8859_3 UTF8 LATIN3
utf8_to_iso_8859_4 UTF8 LATIN4
utf8_to_iso_8859_5 UTF8 ISO_8859_5
utf8_to_iso_8859_6 UTF8 ISO_8859_6
utf8_to_iso_8859_7 UTF8 ISO_8859_7
utf8_to_iso_8859_8 UTF8 ISO_8859_8
utf8_to_iso_8859_9 UTF8 LATIN5
utf8_to_johab UTF8 JOHAB
Tên chuyển đổi a Mã nguồn Mã đích
utf8_to_koi8_r UTF8 KOI8R
utf8_to_koi8_u UTF8 KOI8U
utf8_to_sjis UTF8 SJIS
utf8_to_tcvn UTF8 WIN1258
utf8_to_uhc UTF8 UHC
utf8_to_windows_1250 UTF8 WIN1250
utf8_to_windows_1251 UTF8 WIN1251
utf8_to_windows_1252 UTF8 WIN1252
utf8_to_windows_1253 UTF8 WIN1253
utf8_to_windows_1254 UTF8 WIN1254
utf8_to_windows_1255 UTF8 WIN1255
utf8_to_windows_1256 UTF8 WIN1256
utf8_to_windows_1257 UTF8 WIN1257
utf8_to_windows_866 UTF8 WIN866
utf8_to_windows_874 UTF8 WIN874
windows_1250_to_iso_8859_2 WIN1250 LATIN2
windows_1250_to_mic WIN1250 MULE_INTERNAL
windows_1250_to_utf8 WIN1250 UTF8
windows_1251_to_iso_8859_5 WIN1251 ISO_8859_5
windows_1251_to_koi8_r WIN1251 KOI8R
windows_1251_to_mic WIN1251 MULE_INTERNAL
windows_1251_to_utf8 WIN1251 UTF8
windows_1251_to_windows_8 W6I6N1251 WIN866
windows_1252_to_utf8 WIN1252 UTF8
windows_1256_to_utf8 WIN1256 UTF8
windows_866_to_iso_8859_5 WIN866 ISO_8859_5
windows_866_to_koi8_r WIN866 KOI8R
windows_866_to_mic WIN866 MULE_INTERNAL
windows_866_to_utf8 WIN866 UTF8
windows_866_to_windows_12 W5I1N866 WIN
windows_874_to_utf8 WIN874 UTF8
euc_jis_2004_to_utf8 EUC_JIS_2004 UTF8
ut8_to_euc_jis_2004 UTF8 EUC_JIS_2004
shift_jis_2004_to_utf8 SHIFT_JIS_2004 UTF8
ut8_to_shift_jis_2004 UTF8 SHIFT_JIS_2004
euc_jis_2004_to_shift_jis EU2C00J4IS_2004 SHIFT_JIS_2004
shift_jis_2004_to_euc_jis SH2I0F0T4_JIS_2004 EUC_JIS_2004
Tên chuyển đổi a Mã nguồn Mã đích Lưu ý:
a. Các tên chuyển đổi theo một sơ đồ gọi tên tiêu chuẩn: tên chính thức của mã nguồn với tất cả các ký tự không phải là abc được các dấu gạch chân thay thế, được theo sau bằng _to_, được theo sau bằng tên mã đích được xử lý tương tự.
Vì thế, các tên có thể khác nhau với các tên mã hóa theo thói thường.