Re: SQL Query

Shuzi Chen <chen_at_openmarket.com> wrote:
>
> How to write the SQL like the following?
>
> select * from test where name like 'hel[ply]'
> it will return the records which name is help, hell or hely.

What you want to do is called a character class. Oracle does not implement them directly. However, there is a way to perform this type of query using the often over-looked 'translate' function to create a character class.

Try:

    select * from test where name like 'hel_'

       and translate(substr(name,4,1), 'ply?','???#') = '?';

The expression "translate(substr(name,4,1), 'ply?','???#')" extracts the fourth character from "name" and if it matches 'p', 'l', or 'y' converts it to a '?'. Characters which don't match are unchanged, and '?' characters are mapped to a '#' so they don't match.

This is a very powerful technique that has many applications. I have been meaning to write an article about this for some time. Maybe, I'll get around to it now.

--
Bob Poortinga (mailto:bobp_at_tsc.com, http://www.tsc.com/~bobp/)
Technology Service Corp. (http://www.tsc.com/)
Bloomington, Indiana USA