Path: news.cambrium.nl!textnews.cambrium.nl!feeder3.cambriumusenet.nl!feed.tweaknews.nl!postnews.google.com!r24g2000yqd.googlegroups.com!not-for-mail From: Charles Hooper Newsgroups: comp.databases.oracle.misc Subject: Re: Excluding values with numbers Date: Fri, 1 Jan 2010 14:13:31 -0800 (PST) Organization: http://groups.google.com Lines: 122 Message-ID: <0e49ac71-da98-4db6-879b-c6cbab7e3cbc@r24g2000yqd.googlegroups.com> References: <5b741819-135e-4812-a75e-e9342954f42c@h10g2000vbm.googlegroups.com> <92f72abb-3a28-4f40-822c-431b928acf2b@o28g2000yqh.googlegroups.com> <895685f9-0295-439e-93f7-9b45e264f570@n38g2000yqf.googlegroups.com> <8c45e8d0-405f-4fad-9d18-6a0ccf5485e8@n38g2000yqf.googlegroups.com> <4B3D09ED.3070404@gmail.com> <2a3f7009-12a8-45bb-a42f-6f87610f6a19@26g2000yqo.googlegroups.com> <64387ce3-e135-42ff-b135-00f83fe7683f@u41g2000yqe.googlegroups.com> <94349667-27f9-440f-9c51-dfe2214c079b@c3g2000yqd.googlegroups.com> <948e0e28-9efe-4902-9ceb-de2c1ee8b93b@k19g2000yqc.googlegroups.com> NNTP-Posting-Host: 70.212.101.79 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1262384011 2974 127.0.0.1 (1 Jan 2010 22:13:31 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Fri, 1 Jan 2010 22:13:31 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: r24g2000yqd.googlegroups.com; posting-host=70.212.101.79; posting-account=xVXeFwkAAAAz3xgWc6VZyjXxx1jx4jb4 User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.0; Trident/4.0; GTB5; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 3.5.30729; OfficeLiveConnector.1.3; OfficeLivePatch.0.0; .NET CLR 3.0.30729),gzip(gfe),gzip(gfe) Xref: news.cambrium.nl On Jan 1, 12:13=A0pm, Mark D Powell wrote: > Here is a solution that uses a translate function/. =A0My resutl vary > because I could not remember the actual starting letters specified by > the OP as I do not have access to Oracle and the forum at the same > time. =A0I made my solution case sensitive and used "b,g, and h". =A0I > added two rows to ensure at least one row that started with one of the > exclude letters when followed by digits whould appear in the output. > > 1 > select * from t10 > =A0 2 =A0where homework not in ( > =A0 3 =A0 =A0select homework > =A0 4 =A0 =A0from t10 > =A0 5 =A0 =A0where ( substr(homework,1,1) in ('b','g','h') > =A0 6 =A0 =A0and instr(translate(homework,'012345678','999999999'),'9') > > 0 )) > =A0 7 =A0/ > > HOMEWORK > -------------------- > a12345 > A123423 > G452323 > r34323 > n232323 > NB151517 > C0151517 > f9151517 > HE4423 > hxxxxxxx =A0 =A0 =A0 =A0 =A0-- added > gabcdefg =A0 =A0 =A0 =A0 =A0-- added > > 11 rows selected. > > The above assumes that all the data is of the form Letter || digits > and that no data with mixed letters and digits where the presence of > letters should cause the data to not be excluded. =A0The following would > handle data with those rules using something like h123x as a test > case. > > =A0 5 =A0 =A0where ( substr(homework,1,1) in ('b','g','h') > =A0 6 =A0 =A0and =A0 =A0 =A0 replace(translate(substr(homework,2,length > (homework)), > =A0 7 =A0 =A0 =A0 =A0 =A0 =A0'012345678','999999999'),'9','') is null > > Using an upper or lower rtrim depending on case sensitivity desired as > Maxum demostrated does seem a lot slicker of a solution. > > HTH =A0and hoping I did not make some stupid typo -- Mark D Powell Nice example with the TRANSLATE function. If the OP were running Oracle 10g R1 or later the following would also work: (REGEXP_INSTR) SELECT HOMEWORK FROM T10 WHERE REGEXP_INSTR(UPPER(HOMEWORK),'[HBN][0123456789]')<>1; HOMEWORK -------- a12345 A123423 g13452 G452323 r34323 NB151517 C0151517 f9151517 HE4423 (Shortened version of the above) SELECT HOMEWORK FROM T10 WHERE REGEXP_INSTR(UPPER(HOMEWORK),'[HBN][0-9]')<>1 HOMEWORK -------- a12345 A123423 g13452 G452323 r34323 NB151517 C0151517 f9151517 HE4423 (REGEXP_REPLACE) SELECT HOMEWORK FROM T10 WHERE REGEXP_REPLACE(SUBSTR(UPPER(HOMEWORK),1,2),'[HBN][0123456789]',NULL) IS NOT NULL; HOMEWORK -------- a12345 A123423 g13452 G452323 r34323 NB151517 C0151517 f9151517 HE4423 There must be a couple more ways to solve this SQL problem. Charles Hooper Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table" http://hoopercharles.wordpress.com/ IT Manager/Oracle DBA K&M Machine-Fabricating, Inc.