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 .
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]');
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;
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;
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;
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.
260 views last month, 2 views today