Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2003
    Posts
    18

    Unanswered: small int field allows nulls question

    Hello,

    I have a feild that is 'small int' and 'allows nulls'.
    Can I have space in the field since it allows nulls?


    Thank you, Yanoroo

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Strictly No !!!
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    NULLS:

    A special value that may be stored in some database columns to represent an unknown, missing, not applicable, or undefined value. Nulls are treated completely differently from ordinary values when evaluating SQL expressions and there are several SQL constructs for dealing with nulls.

    In other words it is not a "space", it's the absence of anything, including itself...


    For example NULL = NULL is not true...

    And yes smallint can be NULL
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    But not space!!
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No. A space is an actual character with an ASCII value that takes up 1 byte of space. You cannot put a space in an int column.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: small int field allows nulls question

    Originally posted by yanoroo
    Can I have space in the field since it allows nulls?
    Guys, I don't think he wants to puit a space in, just having a hard time conceptualizing NULL

    And to reiterate NULL <> ' '
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I bet he does want to put a space in it...

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you guys are cruel

    what he really wants is to put a zero-length string in it


  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The INTTOZEROLENGTHSTRING() function will do that.

  11. #11
    Join Date
    Dec 2003
    Posts
    18
    Thank you. I would remove space(s) from the input before pouplating it to table.

    Yanroo

  12. #12
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    you won the bet blindly blindman
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    WHAT are you guys talking about....

    And yanoroo, what action are you taking? Plus if you're not doing up front validation, do you allow alpha and special chars?

    Anyway try this

    INSERT INTO myTable99 (Col1, ect)
    SELECT CASE WHEN ISNUMERIC(@fld) = 1 THEN @lfd ELSE NULL END, ect
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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