Path: utzoo!utgpu!water!watmath!clyde!att!osu-cis!tut.cis.ohio-state.edu!mailrus!ames!umd5!vrdxhq!daitc!jkrueger
From: jkrueger@daitc.ARPA (Jonathan Krueger)
Newsgroups: comp.databases
Subject: Re: Ingres question....
Message-ID: <123@daitc.ARPA>
Date: 29 May 88 17:00:21 GMT
References: <1251@its63b.ed.ac.uk> <22714@bu-cs.BU.EDU> <722@altger.UUCP>
Reply-To: jkrueger@daitc.UUCP (Jonathan Krueger)
Organization: Defense Applied Information Technology Center, Alexandria VA
Lines: 20

>>I'd like to run a few replace statements as follows:
>>replace p(pages=int1(right(squeeze(p.note6),2))) where p.note6="PP ?"
>>Unfortunately.. a handful of these records can't convert to integers

See the INGRES DBA Guide.  The support for the operation you want to do
is indeed poor in 5.0; you can however find problem rows with queries
like:

retrieve (p.all) where p.note6="PP ?*[A-Z]*"

Note that this only finds uppercase alphas; to rule out other nondigits
requires more tests.  If INGRES supported regular expressions as well
as pattern matching we could write this as one test:

... where p.note6 = "PP ..*[^0-9].*"

But what we really want is the support Uli says 6.0 provides, allow a
bulk load to survive type conversion failures and flag problem rows.

-- Jon