Extracting a string using Regular Expression

[boy007]

Is there a function to extract part of a string using a regular expression? Seems such a simple thing, but I don't seem to be able to find it anywhere. Not too keen on using substring, unless there is a good way of applying it.

It would be something like this (where "REGEX" is a function that takes a value and a regular expression, and "firstname" is the name of a column).

select id, REGEX(firstname, 'aa[a-z]*') from peopleTBL

might return:

***************************
ID Name
324 aardvark
***************************

Anyone know any alternatives?

Thanks.

 

[LKBrwnDBA]

All depends on your RDBMS, for Oracle try:

Select Id, Firstname
  From PeopleTBL
 Where REGEX_LIKE(Firstname, 'aa[a-z]*','i');

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb