Results 1 to 5 of 5

Thread: Parsing Query

  1. #1
    Join Date
    Aug 2008

    Unanswered: Parsing Query

    I appreciate your time on this one. I have data I receive (nothing I can do about the format) in an address field that I need to split up. Or if I can just query certain part of it to get the desired result..that would be fine too (I can manipulate it from there. Here is the scenario.

    The data comes into the field like:

    C/O John 1234 N Fake St


    C/O John Smith 1234 N Fake St

    I need to be able to run a query that will return just "C/O John",
    or "C/O John Smith" or "1234 N Fake St"

    So basically, split the field from the start of the first numeric character on. Since the data is not consistent I can't use the normal Left(), Right() commands that I know. I would prefer to keep this in SQL if at all possible. Any suggestions?

  2. #2
    Join Date
    Aug 2008
    I'm almost there...Just like PATINDEX in SQL, but have to use INSTR instead. here is my statement:

    SELECT LEFT([ADDRESS], Instr(1,[ADDRESS],1)-1)

    The Compare Value the "1" after the [ADDRESS] in the INSTR function needs to be changed to a range of numbers. ie., "1-9".

    In MSSQL the statment would look like:

    Select Left(address, PATINDEX(address, '%[1-9]%)-1)

    Anyone know how to get the numbers in a range in Access?

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    AFAIK there is no equivalent to patindex, or regular expressions in MS Access

    if the address always starts with a number then searching for a number should give you the 1st line of the address.. you would need to search for the forst digit (ie 0..9) and find which digit's returned position came first
    if you had an a line delimiter then it would be easy.

    an alternative my be to use the split function, and then reassmble the pieces as required
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Feb 2004
    One Flump in One Place
    This is the set based version of Healdem's last idea:
    SELECT addresses.address, Min(numbers.number) AS FirstNumber
    FROM numbers, addresses
    WHERE numbers.number Between 1 And Len([addresses].[address]) AND Mid([address],[number],1) Like '[0-9]'
    GROUP BY addresses.address
    You'll need a numbers table like this:

    You can build it in SQL Server and import or (an easy way for access) is create it in Excel and import.

    I don't need to mention that the above is not terribly efficient do I?
    pootle flump
    ur codings are working excelent.

  5. #5
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 17
    I've just done something like this on the Excel forum!

    If you're working in Access, you can write a function in VBA and call it in an SQL statement as you would any of the built in functions. With that in mind...

    Function SplitAddress(strAddress As String, booSide As Boolean) As String
    'Function to either return the name element or address element of strAddress,
    'according to booSide.
    Dim strWorking As String
    Dim strTemp As String
    strWorking = strAddress
    'Parse the name element into strTemp
    Do Until IsNumeric(Left(strWorking,1))
       strTemp = strTemp & Left(strWorking, 1)
       strWorking = Right(strWorking, Len(strWorking)-1
    'Look at booSide.  If it's true, return the name.  Otherwise, return the address
    If booSide Then
       SplitAddress = Trim(strTemp)  'There's a spare space on the end of strTemp
       SplitAddress = strWorking
    End If
    Exit Function
    This is untested!
    To use it, add it to the SELECT clause, eg "SELECT SplitAddress([AddressField], True) as [Name], SplitAddress([AddressField], False) as [Address]..."

    Good luck!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts