?
Solved

Microsoft Dynamics AX 2009 Address Line parsing

Posted on 2011-10-28
3
Medium Priority
?
649 Views
Last Modified: 2012-05-12
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
Comment
Question by:mossmis
  • 2
3 Comments
 
LVL 18

Expert Comment

by:Steve Endow
ID: 37048874
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
 

Accepted Solution

by:
mossmis earned 0 total points
ID: 37078648
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
 

Author Closing Comment

by:mossmis
ID: 37098933
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For cloud, the “train has left the station” and in the Microsoft ERP & CRM world, that means the next generation of enterprise software from Microsoft is here: Dynamics 365 is Microsoft’s new integrated business solution that unifies CRM and ERP fun…
Desired Skill Set for Microsoft Dynamics CRM Technical Resources – Part I
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question