[PGS9.2] text match with like, ilike, similar to and posix

/********************************************************************************************
-- 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]