|
tech
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
name parserI have a couple. What format are the names in?
Basically it would look something like this for: Last, First MI SELECT [Conversion Data].[SS#] AS Expr1, [Conversion Data].Name AS Expr2, InStr(1,[Name],', ') AS LFind, Mid([Name],1,[LFind]-1) AS [Last], InStr([LFind]+2,[Name],' ') AS FFind, IIf([FFind]=0,Mid([Name],[LFind]+2),Mid([Name],[LFind]+2,[FFind]-[LFind]-2)) AS [First], IIf([FFind]=0,Null,Mid([Name],[FFind]+1)) AS Middle FROM [Conversion Data]; If you let me know the format, I can easily tailor the query. Thanks, Roger Show quote "mcnews" wrote: > anybody willing to share a name parsing routine? > tia, > mcnewsxp > Roger's solution will probably be fairly close; however, you will not find
any name parser that can handle every possible name. It is second only to parsing street addresses in frustration. Here are some sticky ones: Hyphenated names: Rebbeca Crouton-Rimshot First or Last names with spaces: Farley Mojo St. James names with multiple middle names: James Edwin Autin Healy confusing ones: Benjiman Frank Junior Is the last name Frank or Junior? And a real life example in an application I did in a different version of Basic with addresses. To write the code, we used the USPS addressing rules on how to read addresses. There are prefixes: West, South, NorthEast, etc. There are suffixes: same thing There are street types: Boulevard, Street, Road, etc. with corresponding abbreviations. So in testing we found a street address in Houston Texas that is a real address: 400 West Loop South It failed because West is a prefix, Loop is a street type, and South is a suffix. The street name is actually West Loop and South is actually the suffix, but when parsing it could not find a street name. -- Show quoteDave Hargis, Microsoft Access MVP "mcnews" wrote: > anybody willing to share a name parsing routine? > tia, > mcnewsxp > Great point, Dave. I probably should have mentioned that there will almost
always be issues because of names like "Mary Beth". In my example below, I think Beth would end up as the middle name. Thanks, Roger Show quote "Klatuu" wrote: > Roger's solution will probably be fairly close; however, you will not find > any name parser that can handle every possible name. It is second only to > parsing street addresses in frustration. > Here are some sticky ones: > > Hyphenated names: > > Rebbeca Crouton-Rimshot > > First or Last names with spaces: > > Farley Mojo St. James > > names with multiple middle names: > > James Edwin Autin Healy > > confusing ones: > > Benjiman Frank Junior > > Is the last name Frank or Junior? > > And a real life example in an application I did in a different version of > Basic with addresses. To write the code, we used the USPS addressing rules > on how to read addresses. > There are prefixes: West, South, NorthEast, etc. > There are suffixes: same thing > There are street types: Boulevard, Street, Road, etc. with corresponding > abbreviations. > > So in testing we found a street address in Houston Texas that is a real > address: > > 400 West Loop South > > It failed because West is a prefix, Loop is a street type, and South is a > suffix. > The street name is actually West Loop and South is actually the suffix, but > when parsing it could not find a street name. > > -- > Dave Hargis, Microsoft Access MVP > > > "mcnews" wrote: > > > anybody willing to share a name parsing routine? > > tia, > > mcnewsxp > > Roger,
Hope you didn't think I was critizing your work. Not at all. I just wanted the OP to be aware of the issues. I just realized my brother's home address would give an address parser fits: xxxxx Silverwood Bend Lane -- Show quoteDave Hargis, Microsoft Access MVP "Roger Converse" wrote: > Great point, Dave. I probably should have mentioned that there will almost > always be issues because of names like "Mary Beth". In my example below, I > think Beth would end up as the middle name. > > Thanks, > Roger > > "Klatuu" wrote: > > > Roger's solution will probably be fairly close; however, you will not find > > any name parser that can handle every possible name. It is second only to > > parsing street addresses in frustration. > > Here are some sticky ones: > > > > Hyphenated names: > > > > Rebbeca Crouton-Rimshot > > > > First or Last names with spaces: > > > > Farley Mojo St. James > > > > names with multiple middle names: > > > > James Edwin Autin Healy > > > > confusing ones: > > > > Benjiman Frank Junior > > > > Is the last name Frank or Junior? > > > > And a real life example in an application I did in a different version of > > Basic with addresses. To write the code, we used the USPS addressing rules > > on how to read addresses. > > There are prefixes: West, South, NorthEast, etc. > > There are suffixes: same thing > > There are street types: Boulevard, Street, Road, etc. with corresponding > > abbreviations. > > > > So in testing we found a street address in Houston Texas that is a real > > address: > > > > 400 West Loop South > > > > It failed because West is a prefix, Loop is a street type, and South is a > > suffix. > > The street name is actually West Loop and South is actually the suffix, but > > when parsing it could not find a street name. > > > > -- > > Dave Hargis, Microsoft Access MVP > > > > > > "mcnews" wrote: > > > > > anybody willing to share a name parsing routine? > > > tia, > > > mcnewsxp > > > I did not think that at all. I do not have a ton of experience, so I don't
get to answer many posts, but when I can I try to "give back". Again, I am glad you brought that up, because surely that will be an issue. Funny about your brother's address and I am a Junior, so my name doesn't exactly work either, and I still forgot. Thanks, Roger Show quote "Klatuu" wrote: > Roger, > Hope you didn't think I was critizing your work. Not at all. I just wanted > the OP to be aware of the issues. > > I just realized my brother's home address would give an address parser fits: > > xxxxx Silverwood Bend Lane > > -- > Dave Hargis, Microsoft Access MVP > > > "Roger Converse" wrote: > > > Great point, Dave. I probably should have mentioned that there will almost > > always be issues because of names like "Mary Beth". In my example below, I > > think Beth would end up as the middle name. > > > > Thanks, > > Roger > > > > "Klatuu" wrote: > > > > > Roger's solution will probably be fairly close; however, you will not find > > > any name parser that can handle every possible name. It is second only to > > > parsing street addresses in frustration. > > > Here are some sticky ones: > > > > > > Hyphenated names: > > > > > > Rebbeca Crouton-Rimshot > > > > > > First or Last names with spaces: > > > > > > Farley Mojo St. James > > > > > > names with multiple middle names: > > > > > > James Edwin Autin Healy > > > > > > confusing ones: > > > > > > Benjiman Frank Junior > > > > > > Is the last name Frank or Junior? > > > > > > And a real life example in an application I did in a different version of > > > Basic with addresses. To write the code, we used the USPS addressing rules > > > on how to read addresses. > > > There are prefixes: West, South, NorthEast, etc. > > > There are suffixes: same thing > > > There are street types: Boulevard, Street, Road, etc. with corresponding > > > abbreviations. > > > > > > So in testing we found a street address in Houston Texas that is a real > > > address: > > > > > > 400 West Loop South > > > > > > It failed because West is a prefix, Loop is a street type, and South is a > > > suffix. > > > The street name is actually West Loop and South is actually the suffix, but > > > when parsing it could not find a street name. > > > > > > -- > > > Dave Hargis, Microsoft Access MVP > > > > > > > > > "mcnews" wrote: > > > > > > > anybody willing to share a name parsing routine? > > > > tia, > > > > mcnewsxp > > > > Experience doesn't matter if you know the answer.
You will still see me ask a question once in a while when I get into an area I haven't been in before. For example, I am doing my first ever adp. It is like a different world. Some code I have been carrying around for years that is universal stuff doesn't work now. -- Show quoteDave Hargis, Microsoft Access MVP "Roger Converse" wrote: > I did not think that at all. I do not have a ton of experience, so I don't > get to answer many posts, but when I can I try to "give back". Again, I am > glad you brought that up, because surely that will be an issue. > > Funny about your brother's address and I am a Junior, so my name doesn't > exactly work either, and I still forgot. > > Thanks, > Roger > > "Klatuu" wrote: > > > Roger, > > Hope you didn't think I was critizing your work. Not at all. I just wanted > > the OP to be aware of the issues. > > > > I just realized my brother's home address would give an address parser fits: > > > > xxxxx Silverwood Bend Lane > > > > -- > > Dave Hargis, Microsoft Access MVP > > > > > > "Roger Converse" wrote: > > > > > Great point, Dave. I probably should have mentioned that there will almost > > > always be issues because of names like "Mary Beth". In my example below, I > > > think Beth would end up as the middle name. > > > > > > Thanks, > > > Roger > > > > > > "Klatuu" wrote: > > > > > > > Roger's solution will probably be fairly close; however, you will not find > > > > any name parser that can handle every possible name. It is second only to > > > > parsing street addresses in frustration. > > > > Here are some sticky ones: > > > > > > > > Hyphenated names: > > > > > > > > Rebbeca Crouton-Rimshot > > > > > > > > First or Last names with spaces: > > > > > > > > Farley Mojo St. James > > > > > > > > names with multiple middle names: > > > > > > > > James Edwin Autin Healy > > > > > > > > confusing ones: > > > > > > > > Benjiman Frank Junior > > > > > > > > Is the last name Frank or Junior? > > > > > > > > And a real life example in an application I did in a different version of > > > > Basic with addresses. To write the code, we used the USPS addressing rules > > > > on how to read addresses. > > > > There are prefixes: West, South, NorthEast, etc. > > > > There are suffixes: same thing > > > > There are street types: Boulevard, Street, Road, etc. with corresponding > > > > abbreviations. > > > > > > > > So in testing we found a street address in Houston Texas that is a real > > > > address: > > > > > > > > 400 West Loop South > > > > > > > > It failed because West is a prefix, Loop is a street type, and South is a > > > > suffix. > > > > The street name is actually West Loop and South is actually the suffix, but > > > > when parsing it could not find a street name. > > > > > > > > -- > > > > Dave Hargis, Microsoft Access MVP > > > > > > > > > > > > "mcnews" wrote: > > > > > > > > > anybody willing to share a name parsing routine? > > > > > tia, > > > > > mcnewsxp > > > > >  |
|||||||||||||||||||||||
Other interesting topics