Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2003
    Posts
    97

    Unanswered: help parsing string

    Hello,

    I have one field called FeeDesc varchar(639) which I need to split into a number of sub-fields. I can do this without problem using SUBSTRING(OfferDesc, 1, 81) AS OFFERDESC1, SUBSTRING(FeeDesc, 82, 81) AS FEEDESC2 , etc. The problem is that the data from these split fields can contain a comma at the very end of the field. For example:

    Code:
    PREMIER $72.99                                                                 ,
    I need to trim the white space between $72.99 and the comma, so the final output should be:

    PREMIER $72.99,

    FEEDESC2 is varchar(80)

    Can someone please let me know how I can accomplish this? Obviously, rtrim() alone won't work (since there's a comma at the end).

    TIA
    Last edited by Pat Phelan; 09-27-11 at 16:05.

  2. #2
    Join Date
    Mar 2003
    Posts
    97
    ACK. The forum is mangling the output, but there's a lot of white space from $72.99 until the comma. I need to get rid of that.

    Thanks

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I wrapped your text so it could be read more easily in code and /code tags (you have to put square brackets around those tags too).

    The run of spaces is a special case of whitespace. Do you need to remove all whitespace, only whitespace before commas, or something different? Do you want to remove all of the whitespace within your FeeDesc, or do you only want to remove whitespace within the output segments?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Mar 2003
    Posts
    97
    Ideally, I the output would be: PREMIER $72.99

    Thank you

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by naceBal View Post
    Ideally, I the output would be: PREMIER $72.99
    Why? Please see my questions above.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Mar 2003
    Posts
    97
    Do you need to remove all whitespace, only whitespace before commas, or something different?
    ---> Only whitespace before commas where the comma is in the last position for that field.

    Do you want to remove all of the whitespace within your FeeDesc, or do you only want to remove whitespace within the output segments?
    ---> output segments

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    DECLARE
       @i			INT
    ,  @c			VARCHAR(255)
    
    SET @c = 'PREMIER $72.99                                                                 ,'
    
    SET @i = PATINDEX('% ,', @c)
    
    IF 0 < @i
       SET @c = RTrim(LEFT(@c, @i)) + ','
    
    SELECT QUOTENAME(@c)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Mar 2003
    Posts
    97
    Pat,

    That code seems to have done the trick. I needed to apply it to the UPDATE statement and so far so good. I need to do some more testing, but wanted to say a big THANK YOU SIR

  9. #9
    Join Date
    Sep 2011
    Posts
    75

    Try this

    Hello,

    You can store that value in a variable and use substring function on it.

    SET @Condition = SUBSTRING(@Condition, 1,
    LEN(@Condition) - 1)

Posting Permissions

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