Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10

Thread: collum = " "

  1. #1
    Regular Coder
    Join Date
    Jan 2012
    Location
    Slovakia
    Posts
    111
    Thanks
    9
    Thanked 0 Times in 0 Posts

    collum = " "

    Hi guys ... i need help ... i want do update to a empty collum. Do you know how? its posible? i tried cookies='null' or cookies=null or NULL,NULL but ... nothing :P

    the cookies collum is a varchar btw.

    UPDATE pocitadlo_online SET timestamp='6' WHERE ip='44.444.44.444' AND cookies=' '

  • #2
    Supreme Master coder! _Aerospace_Eng_'s Avatar
    Join Date
    Dec 2004
    Location
    In a place far, far away...
    Posts
    19,291
    Thanks
    2
    Thanked 1,043 Times in 1,019 Posts
    What field type is cookies?
    ||||If you are getting paid to do a job, don't ask for help on it!||||

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    = NULL would be incorrect. you need to test for cookies IS NULL if it is actually a null value in the column. (I.e. not spaces which you are testing for currently with cookies = ' ')

  • #4
    Regular Coder
    Join Date
    Jan 2012
    Location
    Slovakia
    Posts
    111
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by _Aerospace_Eng_ View Post
    What field type is cookies?
    i said varchar

  • #5
    Regular Coder
    Join Date
    Jan 2012
    Location
    Slovakia
    Posts
    111
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by guelphdad View Post
    = NULL would be incorrect. you need to test for cookies IS NULL if it is actually a null value in the column. (I.e. not spaces which you are testing for currently with cookies = ' ')
    hm... ah sorry i have no idea what did you say :P could you please say it more straightly ? :P or edit my code

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,650
    Thanks
    80
    Thanked 4,636 Times in 4,598 Posts
    Here's what I would do:
    Code:
    UPDATE pocitadlo_online 
    SET timestamp='6' 
    WHERE ip='44.444.44.444' AND TRIM(IFNULL(cookies,'')) = ''
    That should cover all possibilities:
    Code:
    -- NULL
    -- ''   
    -- '          '
    That is, blank strings *AND* strings containing nothing but space characters *AND* NULL values will all be treated the same.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Trki (01-11-2012)

  • #7
    Regular Coder
    Join Date
    Jan 2012
    Location
    Slovakia
    Posts
    111
    Thanks
    9
    Thanked 0 Times in 0 Posts

    ''

    Quote Originally Posted by Old Pedant View Post
    Here's what I would do:
    Code:
    UPDATE pocitadlo_online 
    SET timestamp='6' 
    WHERE ip='44.444.44.444' AND TRIM(IFNULL(cookies,'')) = ''
    That should cover all possibilities:
    Code:
    -- NULL
    -- ''   
    -- '          '
    That is, blank strings *AND* strings containing nothing but space characters *AND* NULL values will all be treated the same.
    thx that was brilliant. I again learned something. But i little be dont understand the full code i understand AND TRIM(without spaces) but... could you explain the next code ... how it means IFNULL(cookies,'')) = ''

    and why it didnt works on cookies=''

    it... is meaning like " if is the value of cookies -> '' equal to '' ?

    *sorry for english

  • #8
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    when you test for a value of NULL you can't use an equals sign.

    SELECT * FROM yourtable WHERE yourcolumn = NULL

    is incorrect

    SELECT * FROM yourtable WHERE yourcolumn IS NULL

    is correct. NULL does not match anything, even another NULL so you can't test with =

    more likely though you were using an empty string with perhaps one or more spaces.

    the TRIM takes care of spaces and the IFNULL also checks for NULL condition so both were done in that example posted by Old Pedant.

  • Users who have thanked guelphdad for this post:

    Trki (01-12-2012)

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,650
    Thanks
    80
    Thanked 4,636 Times in 4,598 Posts
    Also, in the query you showed, you were *NOT* testing for a blank string.

    You were testing for a string with ONE SPACE in it.

    And that is a very very different thing.


    ... AND cookies=' '

    See the space in there? That would *ONLY* match a field that indeed had a single space stored in it.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Trki (01-12-2012)

  • #10
    Regular Coder
    Join Date
    Jan 2012
    Location
    Slovakia
    Posts
    111
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by guelphdad View Post
    when you test for a value of NULL you can't use an equals sign.

    SELECT * FROM yourtable WHERE yourcolumn = NULL

    is incorrect

    SELECT * FROM yourtable WHERE yourcolumn IS NULL

    is correct. NULL does not match anything, even another NULL so you can't test with =

    more likely though you were using an empty string with perhaps one or more spaces.

    the TRIM takes care of spaces and the IFNULL also checks for NULL condition so both were done in that example posted by Old Pedant.
    Quote Originally Posted by Old Pedant View Post
    Also, in the query you showed, you were *NOT* testing for a blank string.

    You were testing for a string with ONE SPACE in it.

    And that is a very very different thing.


    ... AND cookies=' '

    See the space in there? That would *ONLY* match a field that indeed had a single space stored in it.
    Ah yeah ok ... i understand. Thx guys that was a very usefull thing what you explained me right now :P Thx and... if u would have time check this please :/

    http://www.codingforums.com/showthread.php?t=248566


  •  

    Posting Permissions

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