
detecting embedded numbers?
Grant Perkins May 15, 2014 9:54 AM (in response to drpepper _)If you have V7 or later you could consider using the STRIP function to remove all possible characters except numbers and do an ISEMPTY() or LEN()>0 check on what is left. If the result is not empty or has length then there are characters present which, presumably, are numbers.
The jury would be out on whether it is elegant but it would be effective.
Alternatively a nested IF statement checking for a numeric character somewhere in the string would do the trick.
[font="courier"]if(instr("1",[Test Field])>0, "NUMERIC",
if(instr("2",[Test Field])>0, "NUMERIC",
if(instr("3",[Test Field])>0, "NUMERIC",
if(instr("4",[Test Field])>0, "NUMERIC",
if(instr("5",[Test Field])>0, "NUMERIC",
if(instr("6",[Test Field])>0, "NUMERIC",
if(instr("7",[Test Field])>0, "NUMERIC",
if(instr("8",[Test Field])>0, "NUMERIC",
if(instr("9",[Test Field])>0, "NUMERIC",
if(instr("0",[Test Field])>0, "NUMERIC",
" " )))))))))) /font[/quote]This will test for a numeric character somewhere in a field and fills the calculated field with NUMERIC if a number exists or leaves it blank if no numeric character is present.
There must be other ways but these are the first to come to mind.
Hope this helps.
Grant

detecting embedded numbers?
Steve Caiels May 15, 2014 9:54 AM (in response to drpepper _)Hi,
If you are trying to filter all records that have a numeric in the postcode filed, then I agree with Grant. The filter expression would be
Len(postcode)<>len(strip(postcode,”1234567890”)
But to split the field, you’d need two expressions. To get the bit before the number, you could try
[font="courier"]if(.not.isalpha(substr(postcode,2,1)),left(postcode,1),
if(.not.isalpha(substr(postcode,3,1)), left(postcode,2),
if(.not.isalpha(substr(postcode,4,1)), left(postcode,3),
if(.not.isalpha(substr(postcode,5,1)), left(postcode,4), if(.not.isalpha(substr(postcode,6,1)), left( postcode,5),
if(.not.isalpha(substr(postcode,7,1)), left(postcode,6),
postcode))))))[/font][/quote]Regards
Steve