extracting the digits only 2

[ziggs]

The database that I'm pulling from using one field for the address (Ex. 123 S Main St.). I was able to strip out everything before the space, which was 123 in this example. Unfortunately, garbage in garbage out. In this new field, I can see for following for a digit field:


123
123B
123-Blk
123-127
123BLK

So, how can I force all to show numeric only and when there's a hyphen present, just show the numerics only before the hyphen.

TIA

 

[Madawc]

You could do it a character at a time. Use the LEFT and MID commands to look at each in turn, then check using IsNumeric.

This would also merge two separate numbers, if you had them. Quite complex logic, but I can't see an easier method.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[ziggs]

Would you mind explaining how to get rid of:
123B to make it 123 only? If I see the isnumeric that would correct the digits, I would hope that I could formulate the rest.

 

[Madawc]

You test the 1, the 2, the 3 and then the B, discard the B. Long winded but I don't know of a simpler way.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Try:

val(replace({table.string},"-","X"))

The "X" is irrelevant except that it prevents the hyphen from being interpreted as part of the resulting number.

-LB