반응형
/****************************************************************************************************************
-- Title : [ORA9.2] String Function ad Operators
-- Reference : www.postgresql.org
-- Key word : char string 스트링 문자열 문자
****************************************************************************************************************/
-- SQL String Functions and Operators
| Function | Return Type | Example | Result |
| string || string | text | 'Post' || 'greSQL' | PostgreSQL |
| string || non-string ornon-string || string | text | 'Value: ' || 42 | Value: 42 |
| bit_length(string) | int | bit_length('jose') | 32 |
| char_length(string) orcharacter_length(string) | int | char_length('jose') | 4 |
| lower(string) | text | lower('TOM') | tom |
| octet_length(string) | int | octet_length('jose') | 4 |
| overlay(string placingstring from int [for int]) | text | overlay('Txxxxas' placing 'hom' from 2 for 4) | Thomas |
| position(substring instring) | int | position('om' in 'Thomas') | 3 |
| substring(string [fromint] [for int]) | text | substring('Thomas' from 2 for 3) | hom |
| substring(string frompattern) | text | substring('Thomas' from '...$') | mas |
| substring(string frompattern for escape) | text | substring('Thomas' from '%#"o_a#"_' for '#') | oma |
| trim([leading | trailing | both] [characters] fromstring) | text | trim(both 'x' from 'xTomxx') | Tom |
| upper(string) | text | upper('tom') | TOM |
-- Other String Functions
| Function | Return Type | Example | Result |
| ascii(string) | int | ascii('x') | 120 |
| btrim(string text [,characters text]) | text | btrim('xyxtrimyyx', 'xy') | trim |
| chr(int) | text | chr(65) | A |
| concat(str "any" [, str"any" [, ...] ]) | text | concat('abcde', 2, NULL, 22) | abcde222 |
| concat_ws(sep text, str"any" [, str "any" [, ...] ]) | text | concat_ws(',', 'abcde', 2, NULL, 22) | abcde,2,22 |
| convert(string bytea,src_encoding name,dest_encoding name) | bytea | convert('text_in_utf8', 'UTF8', 'LATIN1') | text_in_utf8represented in Latin-1 encoding (ISO 8859-1) |
| convert_from(string bytea,src_encoding name) | text | convert_from('text_in_utf8', 'UTF8') | text_in_utf8represented in the current database encoding |
| convert_to(string text,dest_encoding name) | bytea | convert_to('some text', 'UTF8') | some textrepresented in the UTF8 encoding |
| decode(string text, formattext) | bytea | decode('MTIzAAE=', 'base64') | \x3132330001 |
| encode(data bytea, formattext) | text | encode(E'123\\000\\001', 'base64') | MTIzAAE= |
| format(formatstr text [, str"any" [, ...] ]) | text | format('Hello %s, %1$s', 'World') | Hello World, World |
| initcap(string) | text | initcap('hi THOMAS') | Hi Thomas |
| left(str text, n int) | text | left('abcde', 2) | ab |
| length(string) | int | length('jose') | 4 |
| length(string bytea,encoding name ) | int | length('jose', 'UTF8') | 4 |
| lpad(string text, length int[, fill text]) | text | lpad('hi', 5, 'xy') | xyxhi |
| ltrim(string text [,characters text]) | text | ltrim('zzzytrim', 'xyz') | trim |
| md5(string) | text | md5('abc') | 900150983cd24fb0 d6963f7d28e17f72 |
| pg_client_encoding() | name | pg_client_encoding() | SQL_ASCII |
| quote_ident(string text) | text | quote_ident('Foo bar') | "Foo bar" |
| quote_literal(string text) | text | quote_literal(E'O\'Reilly') | 'O''Reilly' |
| quote_literal(valueanyelement) | text | quote_literal(42.5) | '42.5' |
| quote_nullable(string text) | text | quote_nullable(NULL) | NULL |
| quote_nullable(valueanyelement) | text | quote_nullable(42.5) | '42.5' |
| regexp_matches(string text,pattern text [, flags text]) | setof text[] | regexp_matches('foobarbequebaz', '(bar)(beque)') | |
| regexp_replace(string text,pattern text, replacementtext [, flags text]) | text | regexp_replace('Thomas', '.[mN]a.', 'M') | ThM |
| regexp_split_to_array(stringtext, pattern text [, flagstext ]) | text[] | regexp_split_to_array('hello world', E'\\s+') | |
| regexp_split_to_table(stringtext, pattern text [, flagstext]) | setof text | regexp_split_to_table('hello world', E'\\s+') | hello |
| world | |||
| (2 rows) | |||
| repeat(string text, numberint) | text | repeat('Pg', 4) | PgPgPgPg |
| replace(string text, fromtext, to text) | text | replace('abcdefabcdef', 'cd', 'XX') | abXXefabXXef |
| reverse(str) | text | reverse('abcde') | edcba |
| right(str text, n int) | text | right('abcde', 2) | de |
| rpad(string text, length int[, fill text]) | text | rpad('hi', 5, 'xy') | hixyx |
| rtrim(string text [,characters text]) | text | rtrim('trimxxxx', 'x') | trim |
| split_part(string text,delimiter text, field int) | text | split_part('abc~@~def~@~ghi', '~@~', 2) | def |
| strpos(string, substring) | int | strpos('high', 'ig') | 2 |
| substr(string, from [,count]) | text | substr('alphabet', 3, 2) | ph |
| to_ascii(string text [,encoding text]) | text | to_ascii('Karel') | Karel |
| to_hex(number int or bigint) | text | to_hex(2147483647) | 7fffffff |
| translate(string text, fromtext, to text) | text | translate('12345', '143', 'ax') | a2x5 |
반응형