MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » September 2012

Re: SQL select records with numeric strings



fixed

Yes, I'd go with the function. I don't like LIKE because of the bigger
string issue. 12345678923 becoming ++++++++++ and that would be a valid
hit with a LIKE. And, without LIKE, it's hard to omit all non plusses and
do a simple equal.
y=0;
clear BankAccount;
For x=1 to %len(text);
select;
when %subst(text:x:1) >='0' and %subst(text:x:1)<='9';
y+=1;
eval %subst(BankAccount:y:1)=%subst(text:x:1);
when y=9; first non digit after a string of 9 digits
leave;
other; not a digit and y<>9
y=0;
clear BankAccount;
EndFor;
' We hit end of field. If y wasn't 9 then clear out bankAccount.
if y<>9;
clear BankAccount;
Endif;
If BankAccount<>' ';
ShipNumberToNigeria(BankAccount);
EndIf;

Desk check that with a 12 character field with these values:
'123456789 ': Y hit 9, hit a non digit and broke out of loop. Valid
' 123456789': end of field and y=9. Valid
' 123456789 ': y hit 9, hit a non digit and broke out of loop. Valid
'a123456789bc': y hit 9, hit a non digit and broke out of loop. Valid
'abc123456789': end of field and y=9. Valid
'12345 6789': y got up to 5 then reset itself. Only got back up to 4.
invalid
'123456789012'; y>9; invalid

Rob Berendt





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