반응형
/********************************************************************************************
-- Title : [PGSPGS9.2] text match with like, ilike, similar to and posix
-- Reference : postgresql.org, www.oreillynet.com
-- Key word : postgresql text match text matching like ilike similar to posix sensitive insensitiv
                    텍스트매치 텍스트매칭 대문자 소문자 텍스트검색 포식스 escape 이스케이프 문자열
********************************************************************************************/
 
-----------------------
-- Initiate Tables
-----------------------
 
-- Init. Ojbect
drop table ttt;
 
CREATE TABLE ttt(record text);
 
insert into ttt 
values ('a')
     , ('ab')
     , ('abc')
     , ('01234asdf')
     , ('123abc')
     , ('132abc')
     , ('123ABC')
     , ('abc123')
     , ('4567')
     , ('5678')
     , ('6789')
     , ('ABCDEFG')
     , ('1234_abcd')
     , ('abc_123');
 
-------
-- like
-------
 
select * from ttt where record like 'a%';  /*sensitive*/
select * from ttt where record ilike 'a%'; /*insensitive*/
select * from ttt where lower(record) like 'a%';
select * from ttt where record like '%#_%' escape '#'
 
 
----------------------
-- Similar to
---------------------
 
-- Meaning
    o | : denotes alternation (either of two alternatives).
           양자 택일 (2 가지 선택 중 하나)을 의미.
 
    o * : denotes repetition of the previous item zero or more times.
           이전 항목 0 개 이상 복수의 반복을 의미.

    o + : denotes repetition of the previous item one or more times.
           이전 항목 1 회 이상 여러 반복을 의미.

    o ? : denotes repetition of the previous item zero or one time.
           이전 항목의 0 번 또는 1 번 반복을 의미.

    o : denotes repetition of the previous item exactly m times.
               이전 항목의 정확한 m 회 반복을 의미.

    o : denotes repetition of the previous item m or more times.
                이전 항목의 m 번 이상 반복을 의미.

    o : denotes repetition of the previous item at least m and not more than n times.
                  이전 항목의 m 번 이상이고 n 회 이하의 반복을 의미.

    o () : can be used to group items into a single logical item.
            항목을 하나의 논리 항목에 그룹화 가능.

    o [] : specifies a character class, just as in POSIX regular expressions.
            POSIX 정규 표현과 같게 문자 클래스를 지정.
 
-- Using SIMILAR TO(sensitive)
select record
     , record similar to '(a|6)%' "(a|6)%"
     , record similar to '%(8|d)%' "%(8|d)%"
     , record similar to '%#_%' escape '#' "escape"
     , record similar to '%(a*)%' "%(8|d)%"
from ttt;
 
 
-----------------------
-- Posix
----------------------
 
-- Read ttt
select * from ttt;
 
-- matches regular expression, case sensitive
SELECT record FROM ttt WHERE record ~ '1';
SELECT record FROM ttt WHERE record ~ 'a';
SELECT record FROM ttt WHERE record ~ 'A';
SELECT record FROM ttt WHERE record ~ '3a';
 
-- matches regular expression, case insensitive
SELECT record FROM ttt WHERE record ~* 'a';  /* like 'a' */
SELECT record FROM ttt WHERE record ~* '3a';
 
-- not matches regular expression, case sensitive
SELECT record FROM ttt WHERE record !~ 'a';
SELECT record FROM ttt WHERE record !~ 'A';
 
-- not matches regular expression, case insensitive
SELECT record FROM ttt WHERE record !~* 'a';   /* not like 'a' */
SELECT record FROM ttt WHERE record !~* '3a';
 
-- string that begin : caret(^) 
SELECT record FROM ttt WHERE record ~ '^1';
SELECT record FROM ttt WHERE record ~ '^a';
SELECT record FROM ttt WHERE record ~* '^a';
 
-- string that end : dollar($)
SELECT record FROM ttt WHERE record ~ 'c$';
SELECT record FROM ttt WHERE record ~ 'bc$';
SELECT record FROM ttt WHERE record ~* 'bc$';
 
-- or condition : blanket([])
SELECT record FROM ttt WHERE record ~ '[4a]'; /* 4 or a */
SELECT record FROM ttt WHERE record ~ '[ac7]';
SELECT record FROM ttt WHERE record ~ '[a7A]';
SELECT record FROM ttt WHERE record ~* '[ac7]';
 
-- between condition : blanket([-])
SELECT record FROM ttt WHERE record ~ '[2-4]';
SELECT record FROM ttt WHERE record ~* '[a-c5]'; /* [a-c] or 5 */
SELECT record FROM ttt WHERE record ~ '[a-cA-C5-7]';
 
-- and condition : two blankets([][])
SELECT record FROM ttt WHERE record ~ '[a-b][b-c]';
SELECT record FROM ttt WHERE record ~ '[a-b][c-d]';
 
-- don't understand.. :(    
SELECT record FROM ttt WHERE record ~ '[^0-9]';
 
-- or condition : pipe(|)
SELECT record FROM ttt WHERE record ~ '^a|c$';
SELECT record FROM ttt WHERE record ~ 'a|c'; /* equal to [ac] */
SELECT record FROM ttt WHERE record ~ '[ac]';
SELECT record FROM ttt WHERE record ~ '[^0-9|^a-z]';
 
-- Further more Information
   o www.oreillynet.com [1], [2], [3]

 
 
반응형

+ Recent posts