Pages

Monday, December 30, 2013

how to filter records with symbol ~ (tilt)

There is one requirement for customer to select rows which has tilt at the beginning and at the end. After some searching found REGEXP_LIKE New Operator in 10G. Did some test case and seems so simple. Thought to post as I didn't get much detailed info on google.

Rows begins with ~
18:06:37 SQL> select * from test where REGEXP_LIKE (ID,'^~');

ID
--------------------
~test
~1~
~11~
~12~
~32~

Rows begin with ~ and number and ~
18:09:55 SQL> select * from test where REGEXP_LIKE (ID,'^~[0-9]~$');

ID
--------------------
~1~

Rows begin with ~ and any no. of digits and ends with ~
18:11:06 SQL> select * from test where REGEXP_LIKE (ID,'^~[0-9]*~$');

ID
--------------------
~1~
~11~
~12~
~32~

{m} Matches exactly m times
Bracket expression for specifying a matching list that should match any one of the expressions represented in the list. A nonmatching list expression begins with a circumflex (^) and specifies a list that matches any character except for the expressions represented in the list.
^-- beginning of line
$ End of line.

Rows begin with ~ and then 2 numbers and ends with ~
18:11:42 SQL> select * from test where REGEXP_LIKE (ID,'^~[0-9]{2}~$');

ID
--------------------
~11~
~12~
~32~

Thanks for reading, Please comment on post. Happy reading.


Reference:

REGEXP_LIKE New Operator in 10G (Doc ID 255953.1)
http://docs.oracle.com/cd/B19306_01/server.102/b14200/conditions007.htm
http://docs.oracle.com/cd/B19306_01/server.102/b14200/ap_posix001.htm#i690819
http://docs.oracle.com/cd/B19306_01/server.102/b14200/ap_posix002.htm#i690823

No comments:

Post a Comment