REGEXP_LIKE- Examples

View All Scripts Login to Run Script

Script Name REGEXP_LIKE- Examples
Description Validates the given expression for the following: - Whether or not the supplied strings are ANSI compliant dates - Returns only those dates which are YYYY-MM-DD (but doesn't validate whether the DD is greater than 31) - Returns only those dates which are YYYY-MM-DD (but checks for 31 as max DD) - Returns an xml string which has a start and close tags (but doesn't check if the closing tag is same as start tag) - Returns an xml string which has a start and close tags matching.
Area SQL General / Functions
Contributor Oracle
Created Monday October 05, 2015

Statement 1

The dates are supplied as strings and the regexp_like will check for the ANSI date format and returns only those that are in a valid date format. Note: This still returns dates with "99" which are invalid, though the format checking works correctly.

Validate whether the dates are ANSI compliant or not

with dates as (   
  select '2015-01-01' as dt from dual union all /* Valid date 1 Jan 2015 */   
  select '2015-99-99' as dt from dual union all /* Invalid day and month 99 */   
  select '01-01-2015' as dt from dual union all /* In DD-MM-YYYY format */   
  select '2015-12-31' as dt from dual union all /* Valid date 31 Dec 2015 */   
  select '2015-12-39' as dt from dual union all /* Invalid date 39 Dec 2015 */   
  select '2015-31-12' as dt from dual /* Day and month in a wrong way */   
)   
  select * from dates   
  where  regexp_like (dt, '[0-9]{4}-[0-9]{2}-[0-9]{2}')

 
DT
2015-01-01
2015-99-99
2015-12-31
2015-12-39
2015-31-12

5 rows selected.

DT
2015-01-01
2015-99-99
2015-12-31
2015-12-39
2015-31-12

Statement 2

The dates are supplied as strings and regexp_like validates for the specified pattern. Returns the valid date formats. Note: This still returns incorrect dates such as 39 Dec 2015.

Validate whether the dates are ANSI compliant or not

with dates as (   
  select '2015-01-01' as dt from dual union all /* Valid date 1 Jan 2015 */   
  select '2015-99-99' as dt from dual union all /* Invalid day and month 99 */   
  select '01-01-2015' as dt from dual union all /* In DD-MM-YYYY format */   
  select '2015-12-31' as dt from dual union all /* Valid date 31 Dec 2015 */   
  select '2015-12-39' as dt from dual union all /* Invalid date 39 Dec 2015 */   
  select '2015-31-12' as dt from dual /* Day and month in a wrong way */   
)   
  select * from dates   
  where  regexp_like (dt, '[0-9]{4}-[0-1][0-9]-[0-3][0-9]')

 
DT
2015-01-01
2015-12-31
2015-12-39

3 rows selected.

DT
2015-01-01
2015-12-31
2015-12-39

Statement 3

The dates are supplied as strings and regexp_like validates for the specified pattern. Returns the valid date formats. Note: This still returns incorrect dates for the month of February, which can't exceed 28/29.

Validate whether the dates are ANSI compliant or not

with dates as (   
  select '2015-01-01' as dt from dual union all /* Valid date 1 Jan 2015 */   
  select '2015-99-99' as dt from dual union all /* Invalid day and month 99 */   
  select '01-01-2015' as dt from dual union all /* In DD-MM-YYYY format */   
  select '2015-12-31' as dt from dual union all /* Valid date 31 Dec 2015 */   
  select '2015-02-30' as dt from dual union all /* Invalid date 39 Dec 2015 */   
  select '2015-31-12' as dt from dual /* Day and month in a wrong way */  
)   
  select * from dates   
  where  regexp_like (dt, '[0-9]{4}-(0[0-9]|1[0-2])-([0-2][0-9]|3[0-1])')

 
DT
2015-01-01
2015-12-31

2 rows selected.

DT
2015-01-01
2015-12-31

Statement 4

The xml elements are supplied to the query and the regexp_like checks for the closing tag using the "/" character in the string. Note: This example, doesn't check if the opening and closing tags are exactly same or not.

XML validator for checking a closing tag of an element

with xml as (   
  select '<element>test<element>' as x from dual union all /* Incorrect closing tag '/' missing */  
  select '<element>test</different_element>' as x from dual union all /* Different opening and closing tags */  
  select '<element>test</element>' as x from dual /* Valid open and close tags*/  
)   
  select * from xml   
  where  regexp_like (x, '<.*>.*</.*>')

 
X
<element>test</different_element>
<element>test</element>

2 rows selected.

X
<element>test</different_element>
<element>test</element>

Statement 5

The xml elements are supplied to the query and the regexp_like checks for the closing tag using the "/" character in the string. Note: This example checks if the opening and closing tags are exactly same or not, using \1 for back referencing the opening tag.

XML validator for checking the opening and closing tags match

with xml as (   
  select '<element>test<element>' as x from dual union all /* Incorrect closing tag '/' missing */  
  select '<element>test</different_element>' as x from dual union all /* Different opening and closing tags */  
  select '<element>test</element>' as x from dual /* Valid open and close tags*/ 
)   
  select * from xml   
  where  regexp_like (x, '<(.*)>.*</(\1)>')

 
X
<element>test</element>

X
<element>test</element>

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database