Professional Web Applications Themes

leading zero removed from varchar(6) - MySQL

Hi I have a table where a field is described as checkin_time varchar(6). The value might have a leading zero (0730) . When I SELECT checkin_time the leading zero disappears. Any ideas why? Regards John...

  1. #1

    Default leading zero removed from varchar(6)

    Hi

    I have a table where a field is described as checkin_time varchar(6). The
    value might have a leading zero (0730) . When I SELECT checkin_time the
    leading zero disappears. Any ideas why?

    Regards
    John


    John Guest

  2. #2

    Default Re: leading zero removed from varchar(6)

     

    You have to use quotes for strings (single or double, dipends on you
    sql_mode setting):

    mysql> create table t(a varchar(6));
    Query OK, 0 rows affected (0.12 sec)

    mysql> insert into t values (0730);
    Query OK, 1 row affected (0.05 sec)

    mysql> insert into t values ('0730');
    Query OK, 1 row affected (0.04 sec)

    mysql> select * from t;
    +------+
    | a |
    +------+
    | 730 |
    | 0730 |
    +------+
    2 rows in set (0.00 sec)

    mysql> select sql_mode;
    +----------------------------------------------------------------+
    | sql_mode |
    +----------------------------------------------------------------+
    | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTION |
    +----------------------------------------------------------------+
    1 row in set (0.00 sec)

    mysql> insert into t values ("0730");
    Query OK, 1 row affected (0.37 sec)

    mysql> set sql_mode=ANSI_QUOTES;
    Query OK, 0 rows affected (0.00 sec)

    mysql> insert into t values ("0730");
    ERROR 1054 (42S22): Unknown column '0730' in 'field list'


    Regards
    Dimitre


    Radoulov, Guest

  3. #3

    Default Re: leading zero removed from varchar(6)

    John wrote: 

    John,

    It the leading zero disappearing when you select the time, or when you
    display it? What language are you using? And what's your code?

    Languages which don't have strong typing may take this as a numeric
    value and drop leading zeros.
    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  4. #4

    Default Re: leading zero removed from varchar(6)

    Hi folks

    OK, I am using Perl, where $x can be any type of variable (string numeric
    etc)
    I need to recheck that there are quotes surrounding the "0745".
    Will be back.

    Regards
    John


    John Guest

  5. #5

    Default Re: leading zero removed from varchar(6)

    John wrote:
     


    In Perl, if $x contain "0745" and you do any thing numerical with $x, it
    will become 745.

    $ perl -e '$x = "0745";print $x,"\n"'
    0745
    $ perl -e '$x = "0745";$x+=0;print $x,"\n"'
    745


    --
    Brian Wakem
    Email: http://homepage.ntlworld.com/b.wakem/myemail.png
    Brian Guest

  6. #6

    Default Re: leading zero removed from varchar(6)


    Brian Wakem wrote: 
    >
    >
    > In Perl, if $x contain "0745" and you do any thing numerical with $x, it
    > will become 745.
    >
    > $ perl -e '$x = "0745";print $x,"\n"'
    > 0745
    > $ perl -e '$x = "0745";$x+=0;print $x,"\n"'
    > 745[/ref]

    I am just taking a stab in the dark here, but would it make any
    difference using single quotes and not double quotes?

    Daz Guest

  7. #7

    Default Re: leading zero removed from varchar(6)

    Daz wrote:
     
    >>
    >>
    >> In Perl, if $x contain "0745" and you do any thing numerical with $x, it
    >> will become 745.
    >>
    >> $ perl -e '$x = "0745";print $x,"\n"'
    >> 0745
    >> $ perl -e '$x = "0745";$x+=0;print $x,"\n"'
    >> 745[/ref]
    >
    > I am just taking a stab in the dark here, but would it make any
    > difference using single quotes and not double quotes?[/ref]


    No.


    --
    Brian Wakem
    Email: http://homepage.ntlworld.com/b.wakem/myemail.png
    Brian Guest

Similar Threads

  1. Can SYMCCHECKER.DLL be removed?
    By de709q in forum Macromedia Shockwave
    Replies: 0
    Last Post: November 9th, 04:23 PM
  2. VARCHAR or not ?
    By Dirk Moolman in forum Informix
    Replies: 3
    Last Post: September 9th, 08:07 PM
  3. Just removed a router....
    By Bob G in forum Windows Networking
    Replies: 1
    Last Post: July 15th, 06:03 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139