Professional Web Applications Themes

VARCHAR COLUM with Numeric DATA and leading ZERO - MySQL

I have a colum defined as VARCHAR(2) to store a code which can be 2 digits or 1 digit + 1 character '01' '02'.... '10' '11'... '2A' '2B' looking a display of my tebale, I can see that all codes with a leading 0 are stored as one digit only '1' '2' when querying for the value of this code in my app ( ex : BETWEEN ? AND ? ) , I have to use the 2 first positions of a zip_code as a parameter , '01' .. '09' but this will failed to be compared with the stored ...

  1. #1

    Default VARCHAR COLUM with Numeric DATA and leading ZERO

    I have a colum defined as VARCHAR(2) to store a code which can be 2
    digits or 1 digit + 1 character
    '01' '02'.... '10' '11'... '2A' '2B'

    looking a display of my tebale, I can see that all codes with a leading
    0 are stored as one digit only '1' '2'

    when querying for the value of this code in my app ( ex : BETWEEN ?
    AND ? ) , I have to use the 2 first positions of a zip_code as a
    parameter , '01' .. '09' but this will failed to be compared with the
    stored value...

    How can I keep stored a leading '0' ? ( I am using VARCHAR vs CHAR as
    it is mentionnedin the doc to keep the leading 0, but it seems it's
    not true

    joss

    Josselin Guest

  2. #2

    Default Re: VARCHAR COLUM with Numeric DATA and leading ZERO

    Josselin wrote: 

    If you pass the value '01' into a VARCHAR column in MySQL it will be stored
    as '01'.
    Are you sure that your application isn't performing anything that might be
    considered "arithmatic" on the variable holding the value? This is the most
    common way that a loosely typed language will behave with leading zeros.


    Paul Guest

  3. #3

    Default Re: VARCHAR COLUM with Numeric DATA and leading ZERO

    On 2006-11-25 18:13:31 +0100, "Paul Lautman"
    <com> said:
     
    >
    > If you pass the value '01' into a VARCHAR column in MySQL it will be
    > stored as '01'.
    > Are you sure that your application isn't performing anything that might
    > be considered "arithmatic" on the variable holding the value? This is
    > the most common way that a loosely typed language will behave with
    > leading zeros.[/ref]

    Thanks Paul... I double checked my database, and I was playing around
    with the wrong version.... (before I modified it.... ) that's the
    problem with RoR .. 2 DBs , test and production.. and I modified
    'manually' (CSV load data) one version...

    Josselin Guest

  4. #4

    Default Re: VARCHAR COLUM with Numeric DATA and leading ZERO

    On Sat, 25 Nov 2006 17:25:41 +0100, Josselin wrote: 

    So what you REALLY have is ... '8', '9', '10', '11' ... ? MySQL will
    keep the leading '0' in a VARCHAR just fine. It got lost when the data
    was loaded. If it was loaded via a slightly loosely-typed langauge like
    perl, there's probably no reason to look too deeply at MySQL.

    SELECT SUBSTR(CONCAT('0', my_column), -2)

    will stick a leading '0' on the '1', '2', '3', etc values. That kind of
    s for the WHERE portion.

    So, you should probably FIX your data in the table to include that '0'.

    An appropriate UPDATE .. WHERE CHAR_LENGTH(my_column) > 2; will probably
    do the trick.

    --
    10. I will not interrogate my enemies in the inner sanctum -- a small hotel
    well outside my borders will work just as well.
    --Peter Anspach's list of things to do as an Evil Overlord
    Peter Guest

  5. #5

    Default Re: VARCHAR COLUM with Numeric DATA and leading ZERO

    On 2006-11-28 03:48:40 +0100, "Peter H. Coffin" <com> said:
     

    yes, loaded from an Excel csv file via import LOAD DATA....

    UPDATE .. WHERE CHAR_LENGTH(my_column) > 2; ?
    never used .. I'll try on a test table before...

    thanks

    Josselin Guest

  6. #6

    Default Re: VARCHAR COLUM with Numeric DATA and leading ZERO

    On Tue, 28 Nov 2006 04:18:50 +0100, Josselin wrote: 
    >
    > yes, loaded from an Excel csv file via import LOAD DATA....
    >
    > UPDATE .. WHERE CHAR_LENGTH(my_column) > 2; ?
    > never used .. I'll try on a test table before...[/ref]

    Basically, it's looking for ones that only have 0 or 1 character in
    them. Picked CHAR_LENGTH because I don't know what your characterset is,
    or how likely it is that whatever picked up a multibyte character
    somehow. It'll stand up to some pretty bizarre postcodes. (:

    --
    Progress (n.): The process through which Usenet has evolved from
    smart people in front of dumb terminals to dumb people in front
    of smart terminals.
    -- demon.co.uk
    Peter Guest

  7. #7

    Default Re: VARCHAR COLUM with Numeric DATA and leading ZERO

    On 2006-11-28 04:56:43 +0100, "Peter H. Coffin" <com> said:
     
    >>
    >> yes, loaded from an Excel csv file via import LOAD DATA....
    >>
    >> UPDATE .. WHERE CHAR_LENGTH(my_column) > 2; ?
    >> never used .. I'll try on a test table before...[/ref]
    >
    > Basically, it's looking for ones that only have 0 or 1 character in
    > them. Picked CHAR_LENGTH because I don't know what your characterset is,
    > or how likely it is that whatever picked up a multibyte character
    > somehow. It'll stand up to some pretty bizarre postcodes. (:[/ref]

    yes, I am using UTF-8
    thanks

    Josselin Guest

Similar Threads

  1. leading zero removed from varchar(6)
    By John in forum MySQL
    Replies: 6
    Last Post: October 30th, 12:51 PM
  2. #39056 [NEW]: Interbase NUMERIC data type error
    By ddi at elecom dot ru in forum PHP Bugs
    Replies: 1
    Last Post: October 6th, 06:50 AM
  3. new colum
    By Bishoy George in forum ASP.NET Data Grid Control
    Replies: 4
    Last Post: November 21st, 07:56 AM
  4. Converting from data type varchar to data type money
    By schaudry in forum Coldfusion - Advanced Techniques
    Replies: 6
    Last Post: June 10th, 11:01 PM
  5. Converting data type varchar to data type money
    By schaudry in forum Coldfusion - Getting Started
    Replies: 1
    Last Post: June 10th, 07:22 PM

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