Site icon IT Tutorial

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');

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 :

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 .

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.

Exit mobile version