Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!cyclone1.gnilink.net!wn11feed!worldnet.att.net!204.127.198.204!attbi_feed4!attbi.com!rwcrnsc54.POSTED!not-for-mail From: John Russell Newsgroups: comp.databases.oracle.server Subject: Re: select non-alpha Message-ID: References: <3DF7D654.570AC713@exesolutions.com> X-Newsreader: Forte Agent 1.92/32.572 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Lines: 21 NNTP-Posting-Host: 12.233.47.150 X-Complaints-To: abuse@attbi.com X-Trace: rwcrnsc54 1039674508 12.233.47.150 (Thu, 12 Dec 2002 06:28:28 GMT) NNTP-Posting-Date: Thu, 12 Dec 2002 06:28:28 GMT Organization: AT&T Broadband Date: Thu, 12 Dec 2002 06:28:28 GMT Xref: newsfeed1.easynews.com comp.databases.oracle.server:169482 X-Received-Date: Wed, 11 Dec 2002 23:28:26 MST (news.easynews.com) On Thu, 12 Dec 2002 00:20:46 GMT, damorgan wrote: >Pucko wrote: > >> How to write sql /plsql to select all (rows)varchar that does NOT contain >> A-Z or a-z >> >> /pucko > >Work SELECT TRANSLATE(UPPER(field), '2ABCDEFGHIJKLMNOPQRSTUVWXYZ', '2') > >into your code and see if the length of the string changes. How about WHERE UPPER(field) = LOWER(field)? If any letter that is affected by UPPER or LOWER is in the field (i.e. a-z, A-Z), the comparison will fail and the row won't match. The question of how this applies to accented letters is left as an exercise for the reader. :-) John