MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » October 2012

Re: SQL how to fix



fixed

On 30 Oct 2012 14:49, Stone, Joel wrote:
<<SNIP>>
where translate(left(amtxt,65),'+','0123456789','+')
like '%+++++++++%'


In the old thread "Subject: SQL select records with numeric strings"
http://archive.midrange.com/midrange-l/201209/threads.html#00612
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 '+' character.

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 false-positives:

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.






Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact