Extract number only from address field

[jdttek]

Hi
I have an address field that I need to extract only the number portion from the entire string. Length of each string is different, so using right / left does not work. Need Some way to identify only the numerals (1,2,3...) vs. text characters and return only the numbers. Any ideas or suggestions much appreciated.

Thanks
JDTTEK

 

[THoey]

What is the format for the data, something like:

123 Main Street

If so, use the INSTR() function to look for the space between "123" and "Main" and then you can still use the LEFT() function. Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[PaulBricker]

If numeric value is always first in the string you can use
Val([FieldName])
in Terry's example that would return
123

Paul

 

[MichaelRed]

There are alternative formats for addresses, such as:

123 Main St

234 Apple Ave., Apt. 12

#17B Orange Grove Rt. 17

Tower 2, 1445

Savoy Hotel, Suite 7

So, If the addresses are truly generalized you will need quite a bit more than just 'extracting the numerals'. One approach would be to define some 'patterns' and use them as cmpairisons to the form / format of the specifc address. When matches are found, extract the numerals from their 'positioning' information of the pattern, and where there is not a match, refer the extraction process to a manual process.

With some work, (review samples and sample exceptions to modify the patterns) you should be able to at least reduce the manual work load, but this approach will almost certainly leave some part which is simply not ammenable to machine interpertation alone.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[THoey]

You are 100% correct MichaelRed. But, jdttek hasn't responded back, so we don't know what format they have. Maybe they found a solution elsewhere... %-( Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...