On 30 Oct 2012 14:49, Stone, Joel wrote:
In the old thread "Subject: SQL select records with numeric strings"
I seem to recall that the above WHERE clause was described as intending
to select rows where there is a 9-digit string. I do not recall anyone
explicitly mentioning that the use of the plus sign can effect more
false-positives than when using a digit into which to translate all
digits; though Rob mentioned using the digit '1' instead of the '+'
FWiW for amtxt='the value +12345678 is not a 9-digit account number',
that row would be selected even though only an 8-digit number is in that
string. Using the following predicate would resolve that type of
false-positive issue, although there would remain a problem for longer
digits-only strings [e.g. a 10-digit number] that could still result in
translate(left(amtxt,65),'9','012345678','9') like '%999999999%'
FWiW a UDF such as a "regex" function or a function specific to the
task, could eliminate the use of the [effective or actual] TRANSLATE
scalar in the query. That would bypass the CQE requirement\restriction
diagnosed by the sqlcode=-255, thus allowing the OLAP query to be
processed with success using the SQE.