Q: How to do MATCHES with LIKE, etc

From: Niles Ritter <ritter_at_earthlink.net>
Date: 1997/08/08
Message-ID: <ritter-0808970909200001@news.earthlink.net>#1/1

In Informix there is a nice regular-expression operator MATCHES, where you can say

SELECT * FROM table_x WHERE table_x.colm MATCHES '*[A-K]*[0-9]'

and it will find all rows which contain a letter from A to K, followed later by a digit. With SQL Server, the LIKE operator can be used for this with:

SELECT * FROM table_x WHERE table_x.colm MATCHES '%[A-K]%[0-9]'

but in ORACLE, only the percent and underscore are permissible meta-characters in LIKE. So, how does one get the same set of rows in ORACLE, using SUBSTRING's, BETWEEN, etc. ? What I'm looking for is a general way of doing MATCHES regular-expressions, and not an ad-hoc solution to this particular example, unless it is illustrative.

--Niles Ritter Received on Fri Aug 08 1997 - 00:00:00 CDT