Results 1 to 3 of 3

Thread: Parse Field SQL

  1. #1
    Star Lounger
    Join Date
    Jun 2009
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parse Field SQL

    Hello,

    I have a field (Description) in my query that returns an invoice description, for example:

    Description
    Supplies (INV-000001)
    Bookjs (INV-000002)

    I need to parse the following into a new field:

    Invoice
    INV-000001
    INV-000002

    Is there any easy way to accomplish this via SQL?

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Try something like mid([Description], instr(1, [Description], "(" ) +1, 10) This assumes the Invoice number is always ten characters.
    Richard

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by Aheron View Post
    Try something like mid([Description], instr(1, [Description], "(" ) +1, 10) This assumes the Invoice number is always ten characters.
    If the number of characters in the Invoice number part could change you can use:
    Mid([Description],InStr(1,[Description],"(")+1,Len([Description])-InStr(1,[Description],"(")-1)

    The last bit works out how many characters between the ( and the ).
    Regards
    John



Posting Permissions

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