Post reply

Stripping Out Quotes

  • July 15, 2005 at 1:12 am

    #574682

    It's my impession too that an rtrim is performed when comparing strings. That's why strange things may happen:

    declare

    @a varchar(1),

    @b-2 varchar(1),

    @C varchar(1)

    select @a = ''

    select @b-2 = ' '

    select @C = 'x'

    if @a = @b-2

     select 'a = b'                 -- returns a = b

    else

     select 'a != b'

    if @a + @C = @b-2 + @C

     select 'a+c = b+c'

    else

     select 'a+c != b+c'         -- returns a + c != b + c

     

    Thus a = b, but if you add c to both sides of the equation, you get different results (and this time, my db is on 80 level :rolleyes.

    Also select charindex(@a, ' ') and select charindex(@b, ' ') are different. But len(@a) = len(@b) = 0.

    I would definitely have preferred that '' and ' ' were not equal. Does anyone know a reason why they are equal?

  • July 15, 2005 at 5:19 am

    #574726

    Check out Farrell Keough's answer on the previous page.

  • July 15, 2005 at 5:36 am

    #574738

    Oh, I skipped that one after reading the first two lines

    I don't understand his answer (that the result is unknown). Since '' = ' ' returns true and '' != ' ' returns false it looks very much as if they are equal (i.e the result is known). (And unlike what he writes, they both have length 0 in my db).

     

  • July 15, 2005 at 5:46 am

    #574742

    Make a request to steve...
    I can live with that - new avatar for Bully:-
     
    Especially for Remi:-
     
    "I know that you believe that you understood what you think I said, but I am not sure you realize that what you heard is not what I meant."
     
    Have Fun

    Steve

    We need men who can dream of things that never were.

  • July 15, 2005 at 6:26 am

    #574749

    Can you post the image on the net.. we can't see squat if it's on your HD.

    Also what is that message supposed to mean?

  • July 15, 2005 at 9:05 am

    #574883

    Sorry,
     
     
    I thought it would get uploaded and embed it in the post.....
     
    Also what is that message supposed to mean? - It's just a quick quote. How many times have you posted a reply to a query, in all its full technical glory - only for the recipient to interpret it incorrectly and act on their interpretation of your reply? Requiring you to put them straight again.....
     
    I have seen this in here on a number of occaisions and felt the quote would be appreciated and a humourous end to another week .
     
    We still don't really have a comprehensive answer to the way Sequel Server is evaluating quoted strings though and there are a few great minds contributing to this thread. Does this mean we have found a bug or is it just a conveniently 'undocumented feature'......
     
    Have a good weekend everyone. Keep up the good work.
     
    And of course.....
     
    Have fun

    Steve

    We need men who can dream of things that never were.

  • July 15, 2005 at 9:13 am

    #574887

    "Also what is that message supposed to mean? - It's just a quick quote. How many times have you posted a reply to a query, in all its full technical glory - only for the recipient to interpret it incorrectly and act on their interpretation of your reply? Requiring you to put them straight again....."

    Too often... and I don't see that stopping anything soon.

    "I have seen this in here on a number of occaisions and felt the quote would be appreciated and a humourous end to another week."

    Always a welcomed way to end the week .

  • July 15, 2005 at 9:17 am

    #574890

    I thaught I could explain it in my own words but I'm still not sure... maybe you should start another thread for this one as my week-end is about ready to kick in .

  • July 15, 2005 at 9:31 am

    #574902

    From BOL:

    Trailing blanks are ignored in comparisons in non-Unicode data; for example, these are equivalent:

    WHERE au_lname = 'White'WHERE au_lname = 'White 'WHERE au_lname = 'White' + SPACE(1)
    and "If the compatibility level is 65 or lower, SQL Server interprets empty strings as single spaces."
    Also, the ANSI_PADDING setting controls whether trailing blanks are stripped from varchar fields (and
trailing zeroes from varbinary).  This doesn't affect comparisons though.

  • July 15, 2005 at 9:47 am

    #574914

    Where did you get that in books online... This is not listed in mine under set ainsi_padding???

  • July 15, 2005 at 9:55 am

    #574921

    It's only odd on the surface: when two strings are compared in T-SQL, the shorter one is first padded with spaces at the right, to make them equal in length, then they are compared.

    So '' = ' ' really goes '' changed to ' ' then tested against ' '.

    This is why you can compare a char(5) and a char(10) for equality.

    Try

    if 'a' = 'a      '

    select 'equal'

    else

    select 'not equal'

    for example.

  • July 15, 2005 at 9:56 am

    #574923

    The first quote is from the topic "Comparison Search Conditions"

    The '' = ' ' issue can be found under "sp_dbcmptlevel" or "Empty Strings (Level 2)"

    ANSI_PADDING is discussed under "Setting Database Options"

    The issue of trailing spaces in comparisons is not discussed under ANSI_PADDING because that setting only affects storing data in varchar fields.  I believe it will ignore trailing spaces whether ANSI_PADDING is ON or OFF.

  • July 15, 2005 at 9:57 am

    #574924

    Thanx for the info... If that doesn't settle it, we'll have to open a case with Microsoft .

  • July 15, 2005 at 2:57 pm

    #575052

    Now I am a Bully, not a BullDozer?   sheeesh..., try and joke around. 

    (Good picture though...). 

    I wasn't born stupid - I had to study.

  • July 15, 2005 at 7:21 pm

    #575078

    I thought you'd have changed your avatar by now .

Viewing 15 posts - 31 through 45 (of 48 total)

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