Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103

    Unanswered: Filtering out extraneous text in a data field.

    I'm trying to filter out extraneous text within a data field, using my select query. The relevant data starts at the left, varies in length (can't simply truncate), is followed by a square (which may be machine code) and the remainder of the unwanted text. When copied/pasted, the square appears as a blank space:

    The problem:
    source_doc_ref
    "MEDICAL 00901A"
    "REGPAY 009001"
    "REGPAY"
    "REGPAY 005033"
    "E-40 00021A"

    Desired:
    source_doc_ref
    "MEDICAL"
    "REGPAY"
    "REGPAY"
    "REGPAY"
    "E-40"

    I'm able to identify the records containing the machine code using Like "* *" (again, you can't see the square), but I haven't yet had luck with the Replace function. Any suggestions for filtering out/replacing the square and all text to the right would be GREATLY appreciated.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    First thing is to identify the "square". It is probably a carriage return, line feed or CrLf. Try to get the ascii value of the character and then you can use that in an expression.

    Code:
     
    SELECT ASC(MID(MyCol, 5, 1))
    FROM MyT
    WHERE MyCol LIKE "E-40*"
    Do you get a result?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103
    Please pardon my lack of knowledge, as I'm not a programmer.........
    I tried your script in SQL analyzer but didn't get a value yet.

    SELECT ASC(MID(source_doc_ref, 5, 1))
    FROM payroll_gl_intf_jrnl_ctrl
    WHERE source_doc_ref LIKE "E-40*"

    Incorrect syntax near the keyword 'ASC'.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Good reason for that - QA = T SQL. My answer = Access SQL - fair enough though - it is an Access Forum after all.

    Code:
    SELECT ASCII(SUBSTRING(MyCol, 5, 1))
    FROM MyT
    WHERE MyCol LIKE "E-40%"
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm off now so - replace the number with the result you get. CrLf = 13 so:

    Code:
     
    SELECT MID(MyCol, Instr(MyCol, CHR(13)+2))
    FROM MyT
    This is, again, JET SQL - you will need to use substring and charindex if you use T-SQL. You will also need the third arg for substring but you can make this a number as big as your field (e.g. use 100 for VarChar(100))

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103
    Thanks for your time/help.

    It looks like the solution is going to be "over my head". I was trying to accomplish this using the expression builder in Access. I can do some basic conditional logic using code builder, but I'm not an Access programmer.

Posting Permissions

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