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 * FROM OGRENCI WHERE AD LIKE 'M%';
SELECT * FROM OGRENCI WHERE REGEXP_LIKE(AD,'M');
-
Sometimes we can even do operations that LIKE cannot do with REGEXP_LIKE .
-
For example , let’s have a list of letters a , b or c :
SELECT * FROM OGRENCI WHERE REGEXP_LIKE (AD,'^[abc]');
REGEXP_INSTR
Like the regexp_like operator, this results in a similar but more advanced result to the INSTR operator . Specific parameters are :
-
Pattern : Our value to be found ,
-
Position : Search start position ,
-
Occurence : How to find ,
-
Parameters : Search type .
-
Now let’s make an example of this statement . In our example , give the position of the letter ‘e’ the second time you find it after the second character , with no case sensitivity :
SELECT REGEXP_INSTR('Regular Expressions','e',2,2,0,'i')pozisyon FROM OGRENCI;
REGEXP_SUBSTR
It is similar and more advanced to the SUBSTR operator .
-
We want the value between two commas in the query we type :
SELECT REGEXP_SUBSTR('500 ORACLE PARKWAY,REDWOOD SHORES,CA',',[^,]+,')SONUC FROM OGRENCI;
REGEXP_REPLACE
It is similar and more advanced to the REPLACE operator . It also includes some capabilities of the TRANSLATE command .
SELECT AD,REPLACE(AD,'e','a')a1, REGEXP_REPLACE(AD,'e','a')a2, TRANSLATE(AD,'ea','***')b1, REGEXP_REPLACE(AD,'[eac]','*')b2, TRANSLATE(AD,'eac','xyz')c1 FROM OGRENCI;
REGEXP_COUNT
It comes with 11G . It is an easy and capable function in terms of use . Let’s illustrate its use with an example :
SELECT AD,REGEXP_COUNT(AD,'a')a1,--a'larınsayısı REGEXP_COUNT(AD,'a',1,'i')b1,--büyük-küçükharfebakma REGEXP_COUNT(AD,'[abc]')a2,--A,B,C toplamsayısı REGEXP_COUNT(AD,'[^ ]+')a3,--kelimesayısı REGEXP_COUNT(AD,'.')a4,--tümkaraktersayısı FROM OGRENCI;
See you in my next post.