OraFAQ Forum: SQL & PL/SQL » Alpha Wildcards in Oracle

Home » SQL & PL/SQL » SQL & PL/SQL » Alpha Wildcards in Oracle

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Alpha Wildcards in Oracle [message #20766]

Wed, 19 June 2002 11:58

Andy Hubbert
Messages: 1
Registered: June 2002

Junior Member

Help,
I need to find ALL data in a table where the entry begins with 3 alphabet characters. Most of the data in my table begins with only 1 aplha character and I need to identify all other data that begins with moret that 1 alpha chracter.

The SQL "should" be

Select * from ae_dt111 where field1 'XXX%';

The X's represent that aplpha characters that I am looking for.

This statement return 0 results even though I know there are quite a bit in there. I need to find out the wildcard for alpha character in Oracle. Thanks in advance

Report message to a moderator

Re: Alpha Wildcards in Oracle [message #20769 is a reply to message #20766]

Wed, 19 June 2002 13:16

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Would something like this work for you? If your non-alpha characters include characters other than 0-9, add them to the function call.

sql>select c from t order by c;
 
C
-----
123AB
3CD77
A12EF
A1BXY
ABC12
XYZ12
 
6 rows selected.
 
sql>select c
  2    from t
  3   where substr(c, 1, 3) = translate(substr(c, 1, 3), '0123456789', ' ')
  4   order by c;
 
C
-----
ABC12
XYZ12

Report message to a moderator

Previous Topic:	desperate for trigger help!
Next Topic:	more trigger help, please

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 14:22:18 CDT 2024