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.
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
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