Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2012
    Posts
    5

    Unanswered: Extract SUB String using SELECT Statement

    Hi All,

    How to extract a portion on based of search criteria from a Column string using SELECT statement.

    I have a table with below structure:

    Table A Structure
    =============
    Name
    Description
    Transaction
    Value

    Name: James
    Description: Redemption: MMTIME AUD, 3.295%, 25.07.12-01.08.12 (90562)
    Transaction:Close Loan: Mortgage SGD, 3.2625%, 24.07.12-07.08.12, 264308
    Value:$1092

    I want to extract 90562 from Description
    and 264308 from Transaction

    but issue is length of the field is not fixed. First field will be between "()" e.g. (90562)
    and second field will be after , e.g. ",264308"

    Appreciate your help.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    consider using the split function
    might have to stuff the logic into a function
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    For Description
    Code:
    Dim StrStart As Integer
    Dim StrEnd As Integer
    
    StrStart = InStr(Me.Description, "(") + 1
    StrEnd = InStrRev(Me.Description, ")")
    DescriptionExtract = Mid(Me.Description, StrStart, (StrEnd - StrStart))


    For Transaction
    Code:
    Dim StrEnd As Integer
    
    StrEnd = InStrRev(Me.Transaction, ",")
    TransactionExtract = Mid(Me.Transaction, StrEnd + 1)


    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Alternatively, you might want to look at normalising your design. You have a block of comma-separated values in two fields in your example, which leads you to this kind of headache. By all means, write reports that join related fields together in that manner, but do not store them as such.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I wasn't in my 'preacher mode' today,so didn't say anything, but weejas is correct! Holding multiple bits of info in a single Field is a big no-no, in Relational Databases!

    It violates the 'atomic theory' of RDs, which mandates 'one bit of data to one Field,' and it leads to having to do this very kind of thing! If your parsing targets, here, were arranged differently in your Fields, it could have been much harder, if not impossible, to parse them out!

    Also, I apologize for my response, i that I didn't pick up on your wanting to do this using a Select statement!

    Linq ;0)>
    Last edited by Missinglinq; 09-04-12 at 14:29.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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