PostgreSQL 8.0.0 �����ĵ���PostgreSQL �й� ������ | ||||
---|---|---|---|---|
Prev | Fast Backward | Chapter 9. �����Ͳ����� | Fast Forward | Next |
�������������ڼ��Ͳ����ַ�����ֵ�ĺ����Ͳ������� ����������е��ִ������������� character�� character varying���� text ��ֵ����������˵�������������г��ĺ��������Դ�����Щ���ͣ� ����ҪС�ĵ��ǣ���ʹ�� character ���͵�ʱ�� �����Զ�����DZ��Ӱ�졣ͨ�����������ĺ���Ҳ�����ڷ��ִ� ���ͣ�����ֻҪ�Ȱ���Щ����ת��Ϊ�ִ�������ʽ�Ϳ����ˡ� ��Щ���������Դ���λ�����͡�
SQL ������һЩ�ִ������� ������ָ������������������� ij���ض��Ĺؼ��֣������Ƕ������ָ������� �������Table 9-5�� ��Щ����Ҳ�������ĺ�������˵��ʵ���ˡ� ������ Table 9-6����
Table 9-5. SQL �ִ������Ͳ�����
���� | �������� | ���� | ���� | ��� |
---|---|---|---|---|
string || string | text | �ִ����� | 'Post' || 'greSQL' | PostgreSQL |
bit_length(string) | integer | �ִ��������λ�ĸ��� | bit_length('jose') | 32 |
char_length(string) �� character_length(string) | integer | �ִ��е��ַ����� | char_length('jose') | 4 |
convert(string using conversion_name) | text | ʹ��ָ����ת�����ָı���롣ת������ͨ�� CREATE CONVERSION ���塣��Ȼϵͳ����һЩԤ�����ת�����֡����� Table 9-7 ��ȡ���õ�ת������ | convert('PostgreSQL' using iso_8859_1_to_utf_8) | Unicode (UTF-8) �����'PostgreSQL' |
lower(string) | text | ���ִ�ת��ΪСд | lower('TOM') | tom |
octet_length(string) | integer | �ִ��е��ֽ��� | octet_length('jose') | 4 |
position(substring in string) | integer | ���������ִ���λ�� | position('om' in 'Thomas') | 3 |
overlay(string placing string from integer [for integer]) | text | �滻���ִ� | overlay('Txxxxas' placing 'hom' from 2 for 4) | Thomas |
position(substring in string) | integer | ָ�������ִ���λ�� | position('om' in 'Thomas') | 3 |
substring(string [from integer] [for integer]) | text | ��ȡ���ִ� | substring('Thomas' from 2 for 3) | hom |
substring(string from pattern) | text | ��ȡƥ�� POSIX ������ʽ�����ִ� | substring('Thomas' from '...$') | mas |
substring(string from pattern for escape) | text | ��ȡƥ��SQL������ʽ�����ִ� | substring('Thomas' from '%#"o_a#"_' for '#') | oma |
trim([leading | trailing | both] [characters] from string) | text | ���ִ� string �� ��ͷ/��β/����/ ɾ��ֻ���� characters ��ȱʡ��һ���հף�������ִ��� | trim(both 'x' from 'xTomxx') | Tom |
upper(string) | text | ���ִ�ת��Ϊ��д�� | upper('tom') | TOM |
���ж�����ִ��������������ã�������Table 9-6�г��� ������Щ���ڲ�����ʵ��Table 9-5�г���SQL���ִ�������
Table 9-6. �����ִ�����
���� | �������� | ���� | ���� | ��� |
---|---|---|---|---|
ascii(text) | integer | ������һ���ַ��� ASCII �� | ascii('x') | 120 |
btrim(string text [, characters text]) | text | �� string ��ͷ�ͽ�βɾ��ֻ������ characters �ȱʡ�ǿհף����ַ�����ִ��� | btrim('xyxtrimyyx','xy') | trim |
chr(integer) | text | ���� ASCII ����ַ� | chr(65) | A |
convert(string text, [src_encoding name,] dest_encoding name) | text | ���ִ�ת��Ϊ dest_encoding . ԭ���ı������� src_encoding ������. ���ʡ���� src_encoding, �����Ϊ���ݿ����. | convert('text_in_unicode', 'UNICODE', 'LATIN1') | �� ISO 8859-1 �����ʾ��text_in_unicode |
decode(string text, type text) | bytea | ��������encode����ģ������ string ����Ķ��������ݽ��롣 �������ͺ�encodeһ���� | decode('MTIzAAE=', 'base64') | 123\000\001 |
encode(data bytea, type text) | text | �Ѷ��������ݱ���Ϊֻ���� ASCII ��ʽ�����ݡ� ֧�ֵ�������base64��hex��escape�� | encode('123\\000\\001', 'base64') | MTIzAAE= |
initcap(text) | text | ��ÿ�����ʵĵ�һ����ĸתΪ��д�������ı���Сд�� ������һϵ����ĸ������ɵ��ַ����÷���ĸ���ַָ��� | initcap('hi thomas') | Hi Thomas |
length(string text) | integer | string ���ַ�����Ŀ | length('jose') | 4 |
lpad(string text, length integer [, fill text]) | text | ͨ������ַ� fill ��ȱʡʱΪ�հף��� �� string ���Ϊ���� length�� ��� string �Ѿ��� length ������ضϣ����ұߣ��� | lpad('hi', 5, 'xy') | xyxhi |
ltrim(string text [, characters text]) | text | ���ִ� string �� ��ͷɾ��ֻ���� characters ��ȱʡ��һ���հף�������ִ��� | ltrim('zzzytrim','xyz') | trim |
md5(string text) | text | ������� string �� MD5 ɢ�У���ʮ�����Ʒ��ؽ���� | md5('abc') | 900150983cd24fb0d6963f7d28e17f72 |
pg_client_encoding() | name | ��ǰ�ͻ��˱������ơ� | pg_client_encoding() | SQL_ASCII |
quote_ident(string text) | text | ���ظ����ִ���һ����������SQL����ִ��ﵱ����ʶ������ʹ�õ���ʽ�� ֻ���ڱ�Ҫ��ʱ��Ż�������ţ�Ҳ����˵������ִ������DZ�ʶ���ַ�����ת����Сд���ַ����� Ƕ������ű�ǡ����д��˫�ݡ� | quote_ident('Foo bar') | "Foo bar" |
quote_literal(string text) | text | ���ظ����ִ���һ����������SQL����ִ��ﵱ���ı�ʹ�õ���ʽ�� Ƕ������źͷ�б�ܱ�ǡ����д��˫�ݡ� | quote_literal('O\'Reilly') | 'O''Reilly' |
repeat(string text, number integer) | text | �ظ� string number �Ρ� | repeat('Pg', 4) | PgPgPgPg |
replace(string text, from text, to text) | text | ���ִ�string����ֵ��������ִ� from �滻�����ִ� to�� | replace('abcdefabcdef', 'cd', 'XX') | abXXefabXXef |
rpad(string text, length integer [, fill text]) | text | ͨ������ַ� fill ��ȱʡʱΪ�հף��� �� string ���Ϊ���� length�� ��� string �Ѿ��� length ������ضϡ� | rpad('hi', 5, 'xy') | hixyx |
rtrim(string text [, character text]) | text | ���ִ� string �� ��βɾ��ֻ���� character ��ȱʡ�Ǹ��հף�������ִ��� | rtrim('trimxxxx','x') | trim |
split_part(string text, delimiter text, field integer) | text | ���� delimiter �ָ� string �������ɵĵ� field �����ִ���һΪ������ | split_part('abc~@~def~@~ghi','~@~',2) | def |
strpos(string, substring) | text | ���������ִ���λ�á����� position(substring in stringһ����������Ҫע�����˳�� ���෴�ģ� | strpos('high','ig') | 2 |
substr(string, from [, count]) | text | ��ȡ���ִ������� substring(string from from for count)һ���� | substr('alphabet', 3, 2) | ph |
to_ascii(text [, encoding]) | text | �� text ����������ת��Ϊ ASCII�� [a] | to_ascii('Karel') | Karel |
to_hex(number integer ���� bigint) | text | �� number ת�������Ӧ��ʮ�����Ʊ�����ʽ�� | to_hex(9223372036854775807) | 7fffffffffffffff |
translate(string text, from text, to text) | text | ���� string �а������κ�ƥ�� from �е��ַ����ַ�ת��Ϊ��Ӧ�� �� to �е��ַ��� | translate('12345', '14', 'ax') | a23x5 |
Notes: a. to_ascii ����ֻ֧�ִ� LATIN1�� LATIN2��LATIN9 �� WIN1250 �������ת���� |
Table 9-7. ���õ�ת��
ת���� [a] | Դ���� | Ŀ�ı��� |
---|---|---|
ascii_to_mic | SQL_ASCII | MULE_INTERNAL |
ascii_to_utf_8 | SQL_ASCII | UNICODE |
big5_to_euc_tw | BIG5 | EUC_TW |
big5_to_mic | BIG5 | MULE_INTERNAL |
big5_to_utf_8 | BIG5 | UNICODE |
euc_cn_to_mic | EUC_CN | MULE_INTERNAL |
euc_cn_to_utf_8 | EUC_CN | UNICODE |
euc_jp_to_mic | EUC_JP | MULE_INTERNAL |
euc_jp_to_sjis | EUC_JP | SJIS |
euc_jp_to_utf_8 | EUC_JP | UNICODE |
euc_kr_to_mic | EUC_KR | MULE_INTERNAL |
euc_kr_to_utf_8 | EUC_KR | UNICODE |
euc_tw_to_big5 | EUC_TW | BIG5 |
euc_tw_to_mic | EUC_TW | MULE_INTERNAL |
euc_tw_to_utf_8 | EUC_TW | UNICODE |
gb18030_to_utf_8 | GB18030 | UNICODE |
gbk_to_utf_8 | GBK | UNICODE |
iso_8859_10_to_utf_8 | LATIN6 | UNICODE |
iso_8859_13_to_utf_8 | LATIN7 | UNICODE |
iso_8859_14_to_utf_8 | LATIN8 | UNICODE |
iso_8859_15_to_utf_8 | LATIN9 | UNICODE |
iso_8859_16_to_utf_8 | LATIN10 | UNICODE |
iso_8859_1_to_mic | LATIN1 | MULE_INTERNAL |
iso_8859_1_to_utf_8 | LATIN1 | UNICODE |
iso_8859_2_to_mic | LATIN2 | MULE_INTERNAL |
iso_8859_2_to_utf_8 | LATIN2 | UNICODE |
iso_8859_2_to_windows_1250 | LATIN2 | WIN1250 |
iso_8859_3_to_mic | LATIN3 | MULE_INTERNAL |
iso_8859_3_to_utf_8 | LATIN3 | UNICODE |
iso_8859_4_to_mic | LATIN4 | MULE_INTERNAL |
iso_8859_4_to_utf_8 | LATIN4 | UNICODE |
iso_8859_5_to_koi8_r | ISO_8859_5 | KOI8 |
iso_8859_5_to_mic | ISO_8859_5 | MULE_INTERNAL |
iso_8859_5_to_utf_8 | ISO_8859_5 | UNICODE |
iso_8859_5_to_windows_1251 | ISO_8859_5 | WIN |
iso_8859_5_to_windows_866 | ISO_8859_5 | ALT |
iso_8859_6_to_utf_8 | ISO_8859_6 | UNICODE |
iso_8859_7_to_utf_8 | ISO_8859_7 | UNICODE |
iso_8859_8_to_utf_8 | ISO_8859_8 | UNICODE |
iso_8859_9_to_utf_8 | LATIN5 | UNICODE |
johab_to_utf_8 | JOHAB | UNICODE |
koi8_r_to_iso_8859_5 | KOI8 | ISO_8859_5 |
koi8_r_to_mic | KOI8 | MULE_INTERNAL |
koi8_r_to_utf_8 | KOI8 | UNICODE |
koi8_r_to_windows_1251 | KOI8 | WIN |
koi8_r_to_windows_866 | KOI8 | ALT |
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 |
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 | KOI8 |
mic_to_sjis | MULE_INTERNAL | SJIS |
mic_to_windows_1250 | MULE_INTERNAL | WIN1250 |
mic_to_windows_1251 | MULE_INTERNAL | WIN |
mic_to_windows_866 | MULE_INTERNAL | ALT |
sjis_to_euc_jp | SJIS | EUC_JP |
sjis_to_mic | SJIS | MULE_INTERNAL |
sjis_to_utf_8 | SJIS | UNICODE |
tcvn_to_utf_8 | TCVN | UNICODE |
uhc_to_utf_8 | UHC | UNICODE |
utf_8_to_ascii | UNICODE | SQL_ASCII |
utf_8_to_big5 | UNICODE | BIG5 |
utf_8_to_euc_cn | UNICODE | EUC_CN |
utf_8_to_euc_jp | UNICODE | EUC_JP |
utf_8_to_euc_kr | UNICODE | EUC_KR |
utf_8_to_euc_tw | UNICODE | EUC_TW |
utf_8_to_gb18030 | UNICODE | GB18030 |
utf_8_to_gbk | UNICODE | GBK |
utf_8_to_iso_8859_1 | UNICODE | LATIN1 |
utf_8_to_iso_8859_10 | UNICODE | LATIN6 |
utf_8_to_iso_8859_13 | UNICODE | LATIN7 |
utf_8_to_iso_8859_14 | UNICODE | LATIN8 |
utf_8_to_iso_8859_15 | UNICODE | LATIN9 |
utf_8_to_iso_8859_16 | UNICODE | LATIN10 |
utf_8_to_iso_8859_2 | UNICODE | LATIN2 |
utf_8_to_iso_8859_3 | UNICODE | LATIN3 |
utf_8_to_iso_8859_4 | UNICODE | LATIN4 |
utf_8_to_iso_8859_5 | UNICODE | ISO_8859_5 |
utf_8_to_iso_8859_6 | UNICODE | ISO_8859_6 |
utf_8_to_iso_8859_7 | UNICODE | ISO_8859_7 |
utf_8_to_iso_8859_8 | UNICODE | ISO_8859_8 |
utf_8_to_iso_8859_9 | UNICODE | LATIN5 |
utf_8_to_johab | UNICODE | JOHAB |
utf_8_to_koi8_r | UNICODE | KOI8 |
utf_8_to_sjis | UNICODE | SJIS |
utf_8_to_tcvn | UNICODE | TCVN |
utf_8_to_uhc | UNICODE | UHC |
utf_8_to_windows_1250 | UNICODE | WIN1250 |
utf_8_to_windows_1251 | UNICODE | WIN |
utf_8_to_windows_1256 | UNICODE | WIN1256 |
utf_8_to_windows_866 | UNICODE | ALT |
utf_8_to_windows_874 | UNICODE | WIN874 |
windows_1250_to_iso_8859_2 | WIN1250 | LATIN2 |
windows_1250_to_mic | WIN1250 | MULE_INTERNAL |
windows_1250_to_utf_8 | WIN1250 | UNICODE |
windows_1251_to_iso_8859_5 | WIN | ISO_8859_5 |
windows_1251_to_koi8_r | WIN | KOI8 |
windows_1251_to_mic | WIN | MULE_INTERNAL |
windows_1251_to_utf_8 | WIN | UNICODE |
windows_1251_to_windows_866 | WIN | ALT |
windows_1256_to_utf_8 | WIN1256 | UNICODE |
windows_866_to_iso_8859_5 | ALT | ISO_8859_5 |
windows_866_to_koi8_r | ALT | KOI8 |
windows_866_to_mic | ALT | MULE_INTERNAL |
windows_866_to_utf_8 | ALT | UNICODE |
windows_866_to_windows_1251 | ALT | WIN |
windows_874_to_utf_8 | WIN874 | UNICODE |
Notes: a. ת������ѭһ����������ģʽ����Դ�����е����з���ĸ�����ַ� ���»����滻��������� _to_��Ȼ������ٸ��� ����ͬ�������Ŀ���������֡������Щ���ֿ��ܺͿͻ��ı������� ��ͬ�� |