Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2002
    Posts
    4

    Unanswered: Is it possible to extract only bracketed portion of text field using query?

    hi folks, I have a text field which contains a mixture of information that is bracketted and not bracketted, with random numbers of characters. Here are examples of 2 records:


    HPV145 (REVD HV114)
    HDKLLLDKFKKK (REVD HD1) KDOLL

    What I want to do is write a query that would extract

    (REVD HV114)
    (REVD HD1)


    If this cannot be done using access, is there perhaps an excel function that could do it? closest I could get was using FIND (*), which accurately found each set of bracketted info, but no idea how to do a mass extraction (its about 4000 records). Am I SOL, or does someone know a way to do it with some code?

    Regards,

    Jarrett

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sure it can be done, but it's the kind of code that's easy to screw up, so i had to go and test it first

    select charstring
    , iif( Instr(charstring,'(') > 0
    , iif( Instr(charstring,')') > 0
    , Mid( charstring
    , Instr(charstring,'(') +1
    , Instr(charstring,')')
    - Instr(charstring,'(') - 1 )
    , null )
    , null )
    as extracted
    from charstringtable

    "if '(' is found, then if ')' is found, then take the substring starting 1 character beyond the '(' and going for a length calculated by the position of the ')' minus the position of the '(' minus 1.... otherwise return nothing... otherwise return nothing"

    Code:
    charstring          extracted
    this one don't
    this one (does)     does
    (at the front) eh   at the front
    in (the middle) eh  the middle
    at (the end)        the end
    only (front 
    only back)  
    two (of) (them)     of
    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    p.s. remove +1 and -1 if you want to include the brackets in the extracted substring


  4. #4
    Join Date
    Aug 2002
    Posts
    4
    cool, thanks I'll try that

    Jarrett

Posting Permissions

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