Post reply

this might be a very simple question, but it''s really baffling me

  • January 23, 2006 at 7:41 am

    #170179

    I've used this statement in a stored procedure :-

    set @sPool = 'w' + @sPool + 's'

    print @sPool

    can someone explain to me why the letter w get's printed along with the contents of @sPool but not the s?

     

  • January 23, 2006 at 8:46 am

    #616467

    You'll have to tell us how you DELAREd your variable @sPool.. also, did you initialize it to a certain value? Or is @sPool an input parameter?

    If I do this:

    declare @sPool varchar(2000)

    set @sPool = 'fishy'

    set @sPool = 'w' + @sPool + 's'

    print @sPool

    -- result is wfishys

  • January 23, 2006 at 8:47 am

    #616470

    that you put in your reply is exactly how I've done it

  • January 23, 2006 at 9:40 am

    #616479

    Mick,

    I'm using SQL Server 2000 sp3 and I'm getting the 's' on the end of my string. So if you're not, you must be doing something differently. What size are you declaring the string variable? Is it varchar? What is it getting set to? Are you sure there aren't a bunch of spaces or a return char at the end of the input string?

  • January 23, 2006 at 9:43 am

    #616480

    Greg, you've actually found out the problem, I've tested the variable and it seems to be growing in length, it has a lot of spaces adding to the end of the variable, I'm trying to find out how to strip the spaces off the end, any clues? in VB i'd use something like @sVar =Trim(@sVar) and that strips away all the trailing spaces. What's the syntax in SQL?

  • January 23, 2006 at 10:31 am

    #616493

    RTRIM('ABCD ') strips off trailing whitespace

  • January 24, 2006 at 2:26 am

    #616599

    how would that be used with a variable? or am I being thick here?

  • January 24, 2006 at 3:49 am

    #616613

    Hi,

    try

    set @sPool = 'w' + COALESCE(RTRIM(@sPool),'') + 's'

    I added the COALESCE to avoid errors if @sPool should be NULL at some point...

    regards karl

    Best regards
    karl

  • January 24, 2006 at 3:57 am

    #616614

    Excellant, thanks Karl

  • January 24, 2006 at 5:34 am

    #616624

    Hi Mick,

    "I've tested the variable and it seems to be growing in length, it has a lot of spaces adding to the end of the variable"

    Do you know why your variable grows in length? If it is inevitable and you know what causes this behavior, fine... if not, maybe it would be safer to look for the cause of it, not just strip the trailing blanks away. It is always better to intercept problems than solve them afterwards.

  • January 24, 2006 at 10:14 am

    #616722

    You declared it as CHAR(2000), rather than a VARCHAR. CHAR automatically pads from the end of the word all the way to "n" where CHAR(n).

  • January 25, 2006 at 10:34 pm

    #617076

    set @sPool = 'w' + RTRIM(@sPool) + 's'

    print @sPool

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply