Oracle SQL Tutorial -14 Regular Expressions

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.