Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2005
    Posts
    165

    Unanswered: insert semi-colon

    Hi. I need to insert a semicolon into one of my fields in a sql server 2000 database. I have a height column and i was trying to insert height. i know i can't insert 4'5" b/c of the apostrophe and double quote, so i was trying to insert it like:
    Code:
    4'7"
    now i'm running into a problem with the semicolons. how can i insert the semicolons? thanks!

    edit:
    aparently this is removing the same things: here's what i'm talking about:
    http://www.thoughtreactor.com/img-0009.png
    Last edited by bla4free; 02-22-07 at 10:59.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Code:
    insert into theTable (theColumn) values (';');
    Where is the problem?

  3. #3
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by bla4free
    Hi. I need to insert a semicolon into one of my fields in a sql server 2000 database. I have a height column and i was trying to insert height. i know i can't insert 4'5" b/c of the apostrophe and double quote, so i was trying to insert it like:
    Code:
    4'7"
    now i'm running into a problem with the semicolons. how can i insert the semicolons? thanks!

    edit:
    aparently this is removing the same things: here's what i'm talking about:
    http://www.thoughtreactor.com/img-0009.png
    Read up on escape characters in BOL.

    Code:
    create table #specialk (colid int identity, char_desc varchar(20), special_char varchar(10))
    insert into #specialk (char_desc, special_char)
    SELECT 'APOSTROPHE', ''''
    UNION
    SELECT 'QUOTE', '"'
    UNION
    SELECT 'SEMICOLON', ';'
    UNION
    SELECT 'Height', '4'' 7"'
    SELECT * FROM #specialk order by colid
    drop table #specialk
    --  OUTPUT
    colid       char_desc            special_char 
    ----------- -------------------- ------------ 
    1           APOSTROPHE           '
    2           Height               4' 7"
    3           QUOTE                "
    4           SEMICOLON            ;
    (4 row(s) affected)

    -- This is all just a Figment of my Imagination --

  4. #4
    Join Date
    Jan 2005
    Posts
    165
    i have a dropdown menu where the user selects the height. it looks like the picture i posted above. whenever it inputs the value, it says i have an error and it's near the semicolon.

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    It looks like you are not inserting the data with escape characters ... go read and play with escape characters.

    I've pointed you toward a solution - I'm not going to solve it for you.

    I'm showing you how to fish ... not filleting it for you.

    -- This is all just a Figment of my Imagination --

  6. #6
    Join Date
    Jan 2005
    Posts
    165
    Quote Originally Posted by tomh53
    It looks like you are not inserting the data with escape characters ... go read and play with escape characters.

    I've pointed you toward a solution - I'm not going to solve it for you.

    I'm showing you how to fish ... not filleting it for you.
    well, i fiddled with it for a little while and had no luck so I decided to take the step and re-normalize my database and just have a separate table for the height description. but thank you for pointing me in the right direction!

  7. #7
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    also, it might make sense for you to store height in inches only, in an int column, rather than a combination of feet and inches in a char column. Then you could easily do things like sort by height, etc.

    your client app could easily convert the number 65 to 5' 5" if that's the requirement.

    using a character type to store what's really a number is a bad design.

  8. #8
    Join Date
    Jan 2005
    Posts
    165
    Quote Originally Posted by jezemine
    also, it might make sense for you to store height in inches only, in an int column, rather than a combination of feet and inches in a char column. Then you could easily do things like sort by height, etc.

    your client app could easily convert the number 65 to 5' 5" if that's the requirement.

    using a character type to store what's really a number is a bad design.
    that's exactly what i did. at first i started out with 4'7" but i thought what if we wanted to find the average height of people? so i decided to put it in inches. thanks for your help though!

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well then you wouldn't be storing "display" data.

    Either, store the height in decimal form, or use 2 columns, 1 for feet and one for inches
    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.

  10. #10
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I would never use two columns for the same measure, in this case a length. sorting would not work at all if someone puts 0 for the feet and 100 for the inches...

    if 2 columns is good, why not 4 columns? one for hands, one for furlongs, one for fathoms, one for millimeters?

    use one column, and make it a number. fine to use a decimal if you care about fractions of inches. here's a tip: include the units in the name of the column so it's crystal clear: HeightInches

  11. #11
    Join Date
    Feb 2007
    Posts
    62
    It's all about using parameterised queries/statements in your client. I'd read up on that even in preference to escape characters which you rarely need to bother with.
    FWIW I agree that you should be storing inches only. Is 4'7" really less than 6"? It is if you store it as a varchar and order by height.
    Personally I'd use cm

  12. #12
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by LoztInSpace
    Personally I'd use cm
    Me as well, or mm if it should be accurate

Posting Permissions

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