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

    Unanswered: Truncating Text in Select Query

    Using the expression builder in a select query, how can I truncate a carriage return (CHR(9)) and all text to the right within a data field?

    Current Column Data (carriage return appears as blank space):
    "Smith,John 1234ABC"

    Desired Column Data:
    "Smith,John"

    Bytes to the left of the carriage return will vary depending on length of name. I've also been experimenting with the Replace function with no success.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    try

    left$(yourField, instr(yourField, chr$(9))

    izy

    LATER: probably
    left$(yourField, instr(yourField, chr$(9))-1)
    is better
    currently using SS 2008R2

  3. #3
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103
    I tried the latter:

    left$(yourField, instr(yourField, chr$(9))-1)

    However, I'm told the expression may be too complex. I have the aggregate function turned on (grouping). The query runs when I turn off, but the values that should be truncated/altered display as an error.

  4. #4
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103
    Izy, I combined the expression you suggested with an IF/then statement and got the result I was hoping for. Thank you very much!

Posting Permissions

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