Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2010
    Posts
    7

    Smile Unanswered: Access Query find first occurrence of a number or letter?

    Greetings,

    My first post on here in a long long time.

    I was attempting to use regular expression on a like clause to find the first occurence of a letter.

    For an example see below data (address infromation)

    12YellowRoad,PlaceVille,TomatoKingdom
    121YellowRoad,PlaceVille,TomatoKingdom
    1YellowRoad,PlaceVille,TomatoKingdom

    I would like to be able to find out where the first occurence of a letter is then using the replace, could substitue it with a space + the letter to format the address data as I require.

    I was looking at both regular expressions [^[A-z]]* and Instr but have yet to find a solution.

    Any hints or tip would be appreaciated.

    I would like a solution that is SQL based and non VBA if possible.

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use the wild card symbol in a where clause

    UPDATE mytable SET mycolumn = replace(mycolumn,"oldvalue","newvalue")
    WHERE mycolumn like ("*oldvalue*")

    however the problem is that uses a vba function within JET SQL
    I don't think you can achieve what you want using JET SQL, but I may well be proved wrong

    however that whether that will do what you want precisely is up to discussion
    you may be better off limiting the where clause using say left or similar to analuse a specific chunk of the mycolumn value
    adding a sape cafter the comma nae problem, the simple repalce will do the job

    if this is a one off you may be better if doing this by adding a space after all numbers (ie all 10 variants), but limiting it to , say the first 4/5 characters
    then removing the space before all numbers (all nine variants)
    you wil lneed to do some deft string manipulation using left and id to chop/build the strings as require and it won't be a VBA free piece of SQL
    Last edited by healdem; 10-02-10 at 11:27.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2010
    Posts
    7
    Thanks for the reply.

    To clarify I want a solution that can be applied in a query without writing an additonal vba module, just using the SQL view/designer window.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I don't think you can do that.
    you can use regular expressions if you include the relevant library but that means using VBA and VBScript.
    Im not aware of a VBA free way of doing this in JET SQL.
    I suspect you may be force into a Kludgey workaround of
    padding punctuation marks with a trailing space
    padding numbers at the start of the address for the house or building number, then removing the leading space from any number
    then clearing up by removing two or more spaces
    thats some 34 queries, but you could stuff them into a macro and run the macro

    How you handle the hous ebuilding numbers is up to you. I reckon if allow for say 5 digits for the building/house number then you could limit your replace to the fiorst 10 characters.. that will build to say "1 2 3 4 5 ", then tidy up the that by removing leading spaces.
    however you will have a problems with addresses that start with say "suite 10" and so on.

    the real answer in my books is to write a VBA process which sanitises the data and doesn't let it into the db until the data is clean.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jul 2010
    Posts
    7

    Smile

    I have created a query to do the job using regular expressions and the replace function on the update though have to modify it several time to ensure it covers all variations of total digits.

    To potentially improve my query, I would like to know what regular expressions are used within ms access 2003 as it appears to differ than other standards like .net.

    Is there a link for access 2003 regular expressions?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    do a google search for vbscript regular expressions
    you need to include the vbscript library

    however I doubt youcan use vbscript inside a JET SQL statement
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I agree with healdem, seems to be hard (impossible?) without a vba module. If you choose to go this way, you could have a look at: http://www.dbforums.com/microsoft-ac...ml#post6454063
    Have a nice day!

  8. #8
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    The attached database has a single query that parses address lines that are in the predictable format you have given. It would only work for your format and not in the real world where address texts would contain unpredictable characters. For example, 18001STStreet... would be parsed incorrectly (read this as 1800 first street).

    In this scenario, the text must start with a street number, and then be followed by a capitalized letter, and also include the two commas separating the text, as in your examples: 12YellowRoad,PlaceVille,TomatoKingdom

    The query determines the first capitalized letter by checking if the ASCII value is between 65 and 90 (the values of A-Z). My query allows for up to 5 digits in a street number. The text is then recalculated into the street number, followed by a space, and all following text. Next the positions of the two commas are calculated and then the text is recalculated to include a space following both commas.

    The field "AddressLine" at the end of the query fields is the parsed text.
    Jerry
    Attached Files Attached Files

  9. #9
    Join Date
    Jul 2010
    Posts
    7

    Smile

    Thanks Jerry I intended to study this closely soon.

    Appreciated :-)

Posting Permissions

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