Re: SQL Query

Shuzi Chen <chen_at_openmarket.com> writes:

>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.
>Sybase can do exactly like the above. I do not think the Oracle supports
>the [].
 

>Thanks in advance.
>--
>----------------------------------------------------------------------
>Shuzi Chen
>chen_at_openmarket.com

One way to do this:

select * from test where name in ('HELP','HELL','HELY');

In Oracle, there are only two pattern characters used in conjunction with the LIKE clause:

1. The underscore is a one-character wildcard. So,

select * from test where name like 'HEL_';

will return HELP, HELL, HELY....but it will also return HELA, HELB, HELC, etc. if those rows exist in the table.

2) The percent sign is a several-character wildcard...

select * from test where name like 'HEL%';

will return HELP, HELL, HELY....but it will also return ALL records that start with 'HEL', such as 'HELLO', 'HELTER-SKELTER', 'HELOTROPISM', well you get the idea.

Good luck!

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> Visit my Web Page: http://homepage.interaccess.com/~akaplan   <->
<->             email: akaplan_at_interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->