{"id":12072,"date":"2019-11-08T22:18:50","date_gmt":"2019-11-08T22:18:50","guid":{"rendered":"https:\/\/ittutorial.org\/?p=12072"},"modified":"2019-11-20T20:19:13","modified_gmt":"2019-11-20T20:19:13","slug":"regular-expressions","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/regular-expressions\/","title":{"rendered":"Oracle SQL Tutorial -14 Regular Expressions"},"content":{"rendered":"<div class=\"ittut-before-content\" style=\"margin-left: auto;margin-right: auto;text-align: center;\" id=\"ittut-1042479657\"><script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js?client=ca-pub-3373538331699715\" crossorigin=\"anonymous\"><\/script><ins class=\"adsbygoogle\" style=\"display:block;\" data-ad-client=\"ca-pub-3373538331699715\" \ndata-ad-slot=\"1119430155\" \ndata-ad-format=\"auto\"><\/ins>\n<script> \n(adsbygoogle = window.adsbygoogle || []).push({}); \n<\/script>\n<\/div><p align=\"left\"><span style=\"color: #ce181e\"><span style=\"font-size: medium\"><span style=\"color: #000000\">Hi<\/span><span style=\"color: #000000\"> ,<\/span><\/span><\/span><\/p>\n<p align=\"left\"><span style=\"font-size: medium\"><span lang=\"en-US\">In this article I will tell you regular expressions .Regular expressions are used to extract the needed data from the intense data and make the data suitable for the intended use .<\/span> <\/span><\/p>\n<p align=\"left\"><span style=\"color: #0066b3\"><span style=\"font-size: large\">REGEXP_LIKE<\/span><\/span><\/p>\n<p align=\"left\"><span style=\"color: #000000\"><span style=\"font-size: large\"><span lang=\"en-US\">It is similar to the LIKE operator , but provides improved results production than the like operator .<\/span> <\/span><\/span><\/p><div class=\"ittut-content\" id=\"ittut-1788448097\"><script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js?client=ca-pub-3373538331699715\" crossorigin=\"anonymous\"><\/script><ins class=\"adsbygoogle\" style=\"display:block;\" data-ad-client=\"ca-pub-3373538331699715\" \ndata-ad-slot=\"1119430155\" \ndata-ad-format=\"auto\"><\/ins>\n<script> \n(adsbygoogle = window.adsbygoogle || []).push({}); \n<\/script>\n<\/div>\n<pre><em>SELECT * FROM OGRENCI WHERE AD LIKE 'M%';<\/em><\/pre>\n<p align=\"left\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-12074\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/11\/c2-300x48.png\" alt=\"\" width=\"425\" height=\"68\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/11\/c2-300x48.png 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/11\/c2-768x123.png 768w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/11\/c2-800x130.png 800w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/11\/c2.png 810w\" sizes=\"auto, (max-width: 425px) 100vw, 425px\" \/><\/p>\n<pre><em>SELECT * FROM OGRENCI WHERE REGEXP_LIKE(AD,'M');<\/em><\/pre>\n<p align=\"left\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-12076\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/11\/c4-300x48.png\" alt=\"\" width=\"413\" height=\"66\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/11\/c4-300x48.png 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/11\/c4-768x123.png 768w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/11\/c4-800x130.png 800w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/11\/c4.png 810w\" sizes=\"auto, (max-width: 413px) 100vw, 413px\" \/><\/p>\n<ul>\n<li>\n<p align=\"left\"><span style=\"color: #000000\"><span style=\"font-size: large\"><span lang=\"en-US\">Sometimes we can even do operations that LIKE cannot do with REGEXP_LIKE .<\/span> <\/span><\/span><\/p>\n<\/li>\n<li>\n<p align=\"left\"><span style=\"color: #000000\"><span style=\"font-size: large\"><span lang=\"en-US\">For example , let&#8217;s have a list of letters a , b or c :<\/span> <\/span><\/span><\/p>\n<\/li>\n<\/ul>\n<pre><em>SELECT * FROM OGRENCI WHERE REGEXP_LIKE (AD,'^[abc]');<\/em><\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-12078\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/11\/c6-300x25.png\" alt=\"\" width=\"456\" height=\"38\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/11\/c6-300x25.png 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/11\/c6-768x63.png 768w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/11\/c6-800x66.png 800w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/11\/c6.png 806w\" sizes=\"auto, (max-width: 456px) 100vw, 456px\" \/><\/p>\n<p align=\"left\"><span style=\"color: #0066b3\"><span style=\"font-size: large\">REGEXP_INSTR<\/span><\/span><\/p>\n<p align=\"left\"><span style=\"font-size: medium\"><span style=\"color: #000000\"><span lang=\"en-US\">Like the regexp_like operator, this results in a similar but more advanced result to the INSTR operator<\/span><\/span><span style=\"color: #000000\"> . <\/span><span lang=\"en-US\">Specific parameters are :<\/span> <\/span><\/p>\n<ul>\n<li>\n<p align=\"left\"><span style=\"color: #000000\"><span style=\"font-size: large\">Pattern : <\/span><\/span><span lang=\"en-US\">Our value to be found<\/span> <span style=\"color: #000000\"><span style=\"font-size: large\">,<\/span><\/span><\/p>\n<\/li>\n<li>\n<p align=\"left\"><span style=\"color: #000000\"><span style=\"font-size: large\">Position : <\/span><\/span><span lang=\"en-US\">Search start position<\/span><span style=\"color: #000000\"><span style=\"font-size: large\"> ,<\/span><\/span><\/p>\n<\/li>\n<li>\n<p align=\"left\"><span style=\"color: #000000\"><span style=\"font-size: large\">Occurence : <\/span><\/span><span lang=\"en-US\">How to find<\/span> <span style=\"color: #000000\"><span style=\"font-size: large\">,<\/span><\/span><\/p>\n<\/li>\n<li>\n<p align=\"left\"><span style=\"color: #000000\"><span style=\"font-size: large\">Parameters : <\/span><\/span><span lang=\"en-US\">Search type<\/span> <span style=\"color: #000000\"><span style=\"font-size: large\"> .<\/span><\/span><\/p>\n<\/li>\n<li>\n<p align=\"left\"><span style=\"font-size: medium\"><span style=\"color: #000000\"><span lang=\"en-US\">Now let&#8217;s make an example of this statement . <\/span><\/span><span lang=\"en-US\">In our example , give the position of the letter \u2018e\u2019 the second time you find it after the second character , with no case sensitivity : <\/span><\/span><\/p>\n<\/li>\n<\/ul>\n<pre><em>SELECT REGEXP_INSTR('Regular Expressions','e',2,2,0,'i')pozisyon FROM OGRENCI;<\/em><\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-12080\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/11\/c8.png\" alt=\"\" width=\"218\" height=\"168\" \/><\/p>\n<p align=\"left\"><span style=\"color: #0066b3\"><span style=\"font-size: large\">REGEXP_SUBSTR<\/span><\/span><\/p>\n<p align=\"left\"><span style=\"color: #000000\"><span style=\"font-size: large\"><span lang=\"en-US\">It is similar and more advanced to the SUBSTR operator .<\/span> <\/span><\/span><\/p>\n<ul>\n<li>\n<p align=\"left\"><span style=\"color: #000000\"><span style=\"font-size: large\"><span lang=\"en-US\">We want the value between two commas in the query we type :<\/span> <\/span><\/span><\/p>\n<\/li>\n<\/ul>\n<pre><em>SELECT REGEXP_SUBSTR('500 ORACLE PARKWAY,REDWOOD SHORES,CA',',[^,]+,')SONUC FROM OGRENCI;<\/em><\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-12082\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/11\/c10.png\" alt=\"\" width=\"263\" height=\"84\" \/><\/p>\n<p align=\"left\"><span style=\"color: #0066b3\"><span style=\"font-size: large\">REGEXP_REPLACE<\/span><\/span><\/p>\n<p align=\"left\"><span style=\"font-size: medium\"><span style=\"color: #000000\"><span lang=\"en-US\">It is similar and more advanced to the REPLACE operator .<\/span><\/span> <span lang=\"en-US\">It also includes some capabilities of the TRANSLATE command .<\/span> <\/span><\/p>\n<pre><em>SELECT AD,REPLACE(AD,'e','a')a1,<\/em>\r\n<em>REGEXP_REPLACE(AD,'e','a')a2,<\/em>\r\n<em>TRANSLATE(AD,'ea','***')b1,<\/em>\r\n<em>REGEXP_REPLACE(AD,'[eac]','*')b2,<\/em>\r\n<em>TRANSLATE(AD,'eac','xyz')c1 FROM OGRENCI;<\/em><\/pre>\n<p align=\"left\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-12084\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/11\/c12-300x50.png\" alt=\"\" width=\"390\" height=\"65\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/11\/c12-300x50.png 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/11\/c12-768x128.png 768w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/11\/c12.png 959w\" sizes=\"auto, (max-width: 390px) 100vw, 390px\" \/><\/p>\n<p align=\"left\"><span style=\"color: #0066b3\"><span style=\"font-size: large\">REGEXP_COUNT<\/span><\/span><\/p>\n<p align=\"left\"><span style=\"font-size: medium\"><span style=\"color: #000000\"><span lang=\"en-US\">It comes with 11G .<\/span><\/span> <span lang=\"en-US\">It is an easy and capable function in terms of use .<\/span> <span style=\"color: #000000\"><span lang=\"en-US\">Let&#8217;s illustrate its use with an example :<\/span><\/span> <\/span><\/p>\n<pre><em>SELECT AD,REGEXP_COUNT(AD,'a')a1,--a'lar\u0131nsay\u0131s\u0131<\/em>\r\n<em>REGEXP_COUNT(AD,'a',1,'i')b1,--b\u00fcy\u00fck-k\u00fc\u00e7\u00fckharfebakma<\/em>\r\n<em>REGEXP_COUNT(AD,'[abc]')a2,--A,B,C toplamsay\u0131s\u0131<\/em>\r\n<em>REGEXP_COUNT(AD,'[^ ]+')a3,--kelimesay\u0131s\u0131<\/em>\r\n<em>REGEXP_COUNT(AD,'.')a4,--t\u00fcmkaraktersay\u0131s\u0131 FROM OGRENCI;<\/em><\/pre>\n<p align=\"left\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-12086\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/11\/c14-300x49.png\" alt=\"\" width=\"429\" height=\"70\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/11\/c14-300x49.png 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/11\/c14-768x126.png 768w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/11\/c14.png 965w\" sizes=\"auto, (max-width: 429px) 100vw, 429px\" \/><\/p>\n<p lang=\"en-US\" align=\"left\"><span style=\"color: #000000\"><span style=\"font-size: large\"><b>See you in my next post.<\/b><\/span><\/span><\/p>\n<p lang=\"en-US\" align=\"left\">\n<div class=\"ittut-after-content\" id=\"ittut-2703286792\"><script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js?client=ca-pub-3373538331699715\" crossorigin=\"anonymous\"><\/script><ins class=\"adsbygoogle\" style=\"display:block;\" data-ad-client=\"ca-pub-3373538331699715\" \ndata-ad-slot=\"1119430155\" \ndata-ad-format=\"auto\"><\/ins>\n<script> \n(adsbygoogle = window.adsbygoogle || []).push({}); \n<\/script>\n<\/div>","protected":false},"excerpt":{"rendered":"<p>Hi , In this article I will tell you regular expressions .Regular expressions are used to extract the needed data from the intense data and make the data suitable for the intended use . REGEXP_LIKE It is similar to the LIKE operator , but provides improved results production than the like operator . SELECT * &hellip;<\/p>\n","protected":false},"author":10447,"featured_media":12294,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[3019],"tags":[5477,1340,6841,3001,1572,6811,6836,6848,3000],"class_list":["post-12072","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-oracle-sql","tag-how-to-learn-oracle-sql","tag-oracle-sql","tag-oracle-sql-information","tag-oracle-sql-tutorial","tag-sql","tag-sql-information","tag-sql-queries","tag-sql-regular-expressions","tag-sql-tutorial"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/11\/11-using-regular-expressions-with-oracle-database-4-638.jpg","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/12072","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/10447"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=12072"}],"version-history":[{"count":7,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/12072\/revisions"}],"predecessor-version":[{"id":12405,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/12072\/revisions\/12405"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/12294"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=12072"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=12072"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=12072"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}