• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 656
  • Last Modified:

Microsoft Dynamics AX 2009 Address Line parsing

We have Dynamics AX 2009 and I need to format a query to parse the single line address field in AX into separate lines, ie Address1, Address2......etc.

Here is an example of the field in AX SQL DB:
BOB SMITH SPORTING GOODS CO.  9 SPRING LANE BOSTON, MA 02109 US

Any help would be appreciated.
0
mossmis
Asked:
mossmis
  • 2
1 Solution
 
Steve EndowMicrosoft MVP - Dynamics GPCommented:
Hi,

I've had two projects that involved address parsing and have spent many many hours on this topic related to Dynamics GP.

From what I have found, there is no effective way to use SQL or any other home grown code to reliably parse addresses.  You can try, but it is surprisingly difficult to do with US addresses, and virtually impossible to do with international addresses.

The best, most reasonably priced, and practical solution I have found so far for programmatic address parsing is RecogniContact by Loquisoft.  I've discussed it in two blog posts here:

http://dynamicsgpland.blogspot.com/2010/06/importing-unparsed-addresses-into.html

http://dynamicsgpland.blogspot.com/2010/06/importing-unparsed-addresses-into_25.html

Although it can't magically parse every address, it works quite well, and gives you plenty of feedback in terms of what parsed and what didn't.  It took me a while to tweak it for the particular address data that I was working with, but I eventually had very good results with it.  

If you have any questions, let me know.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified IT Professional
0
 
mossmisAuthor Commented:
I did some research and found out address lines are separated via line feeds, '\n' or CHAR(10).

Here is some SQL code I generated from the SQL backend That got me results:

select
SUBSTRING(ADDRESS,1,CHARINDEX(CHAR(10),ADDRESS)-1) as address1,
SUBSTRING (ADDRESS,CHARINDEX(CHAR(10),ADDRESS)+1,LEN(ADDRESS)-CHARINDEX(CHAR(10),ADDRESS)+1) as address2
from dbo.address
0
 
mossmisAuthor Commented:
It's tough to find solutions for Dynamics products online. I got some pointers from a Dynamics AX consultant that told me the address is separated by line feeds. From there, I used CHARINDEX and SUBSTRING to complete my query.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now