Professional Web Applications Themes

fix lenght - MySQL

Hi suppose we want a string to be exactly 7 characters long (like car plates) we can not use varchar(7) because it will also accept stings which are less that 7 characters. Which data type do we have to use for this purpose? -- Thanks in advance Kherse Sefid...

  1. #1

    Default fix lenght

    Hi
    suppose we want a string to be exactly 7 characters long (like car
    plates) we can not use varchar(7) because it will also accept stings
    which are less that 7 characters. Which data type do we have to use for
    this purpose?
    --


    Thanks in advance
    Kherse Sefid
    kherse Guest

  2. #2

    Default Re: fix lenght


    "kherse sefid" <com> wrote in message
    news:f04ods$52g$uio.no... 






    Wouldn't it be nice to have proper check constraints in MySQL.

    This would work, you just need to handle the error that would be thrown by
    someone entering the wrong data.

    CREATE TABLE number_plates
    (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    car_description VARCHAR(50) NOT NULL,
    number_plate VARCHAR(7) NOT NULL
    );

    DELIMETER //
    CREATE TRIGGER check_ai
    AFTER INSERT ON number_plates
    FOR EACH ROW
    BEGIN
    IF length(new.number_plate) < 7 THEN
    DELETE FROM PLATES WHERE id = new.id;
    END IF;
    END//

    CREATE TRIGGER check_au
    AFTER UPDATE ON number_plates
    FOR EACH ROW
    BEGIN
    IF length(new.number_plate) < 7 THEN
    UPDATE PLATES SET
    id = old.id,
    number_plate = old.number_plate,
    car_description = old.car_description
    WHERE id = new.id;
    END IF;
    END//

    DELIMITER ;



    Sean Guest

  3. #3

    Default Re: fix lenght

    On 18.04.2007 13:18, Sean wrote: 
    >
    >
    >
    >
    >
    >
    > Wouldn't it be nice to have proper check constraints in MySQL.
    >
    > This would work, you just need to handle the error that would be thrown by
    > someone entering the wrong data.
    >
    > CREATE TABLE number_plates
    > (
    > id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    > car_description VARCHAR(50) NOT NULL,
    > number_plate VARCHAR(7) NOT NULL
    > );
    >
    > DELIMETER //
    > CREATE TRIGGER check_ai
    > AFTER INSERT ON number_plates
    > FOR EACH ROW
    > BEGIN
    > IF length(new.number_plate) < 7 THEN
    > DELETE FROM PLATES WHERE id = new.id;
    > END IF;
    > END//
    >
    > CREATE TRIGGER check_au
    > AFTER UPDATE ON number_plates
    > FOR EACH ROW
    > BEGIN
    > IF length(new.number_plate) < 7 THEN
    > UPDATE PLATES SET
    > id = old.id,
    > number_plate = old.number_plate,
    > car_description = old.car_description
    > WHERE id = new.id;
    > END IF;
    > END//
    >
    > DELIMITER ;
    >
    >
    >[/ref]

    thanks, it's great, but is there any easy way (like a datatype) we can
    use for this purpose?

    --


    Thanks in advance
    Kherse Sefid
    kherse Guest

  4. #4

    Default Re: fix lenght


    "kherse sefid" <com> wrote in message
    news:f053mo$av3$uio.no... 
    >>
    >>
    >>
    >>
    >>
    >>
    >> Wouldn't it be nice to have proper check constraints in MySQL.
    >>
    >> This would work, you just need to handle the error that would be thrown
    >> by someone entering the wrong data.
    >>
    >> CREATE TABLE number_plates
    >> (
    >> id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    >> car_description VARCHAR(50) NOT NULL,
    >> number_plate VARCHAR(7) NOT NULL
    >> );
    >>
    >> DELIMETER //
    >> CREATE TRIGGER check_ai
    >> AFTER INSERT ON number_plates
    >> FOR EACH ROW
    >> BEGIN
    >> IF length(new.number_plate) < 7 THEN
    >> DELETE FROM PLATES WHERE id = new.id;
    >> END IF;
    >> END//
    >>
    >> CREATE TRIGGER check_au
    >> AFTER UPDATE ON number_plates
    >> FOR EACH ROW
    >> BEGIN
    >> IF length(new.number_plate) < 7 THEN
    >> UPDATE PLATES SET
    >> id = old.id,
    >> number_plate = old.number_plate,
    >> car_description = old.car_description
    >> WHERE id = new.id;
    >> END IF;
    >> END//
    >>
    >> DELIMITER ;
    >>
    >>
    >>[/ref]
    >
    > thanks, it's great, but is there any easy way (like a datatype) we can use
    > for this purpose?
    >
    > --
    >
    >
    > Thanks in advance
    > Kherse Sefid[/ref]


    Not that I can think of ... as you have found out, the standard text type
    only specified the 'max' length. I don't think that there's anyway to
    specify a minimum.



    Sean Guest

  5. #5

    Default Re: fix lenght

    kherse sefid schrieb: 

    Use CHAR.
    Joachim Guest

  6. #6

    Default Re: fix lenght

    On Apr 18, 5:50 pm, Joachim Durchholz <org> wrote: 
    >
    > Use CHAR.[/ref]

    What will that achieve?

    strawberry Guest

  7. #7

    Default Re: fix lenght

    strawberry schrieb: 
    >
    > What will that achieve?[/ref]

    No more and no less than the OP wanted: give you a fixed-length string.
    (With caveats: depending on what exactly you do, trailing blanks may be
    stripped off or not.)

    Regards,
    Jo
    Joachim Guest

  8. #8

    Default Re: fix lenght

    On Apr 18, 8:29 pm, Joachim Durchholz <org> wrote: [/ref]

    >
    > No more and no less than the OP wanted: give you a fixed-length string.
    > (With caveats: depending on what exactly you do, trailing blanks may be
    > stripped off or not.)
    >
    > Regards,
    > Jo[/ref]


    But CHAR(7) will accept values of fewer than 7 characters - or am I
    missing something?

    strawberry Guest

  9. #9

    Default Re: fix lenght

    strawberry schrieb: 

    It will automatically pad it with spaces to 7 characters.

    Some DB access layers will automatically trim trailing spaces from CHAR
    fields when retrieving data, so it may look like a variable-length
    string that cannot store trailing blanks.

    In practice, I don't care too much.
    Either the string is variable-length at the business logic level, then I
    use VARCHAR anyway.
    Or it is fixed-length and doesn't contain spaces, then I use CHAR.
    I haven't seen the case "fixed-length and may have trailing space
    characters" yet.

    Regards,
    Jo
    Joachim Guest

  10. #10

    Default Re: fix lenght

    On 19.04.2007 09:42, Joachim Durchholz wrote: 
    >
    > It will automatically pad it with spaces to 7 characters.
    >
    > Some DB access layers will automatically trim trailing spaces from CHAR
    > fields when retrieving data, so it may look like a variable-length
    > string that cannot store trailing blanks.
    >
    > In practice, I don't care too much.
    > Either the string is variable-length at the business logic level, then I
    > use VARCHAR anyway.
    > Or it is fixed-length and doesn't contain spaces, then I use CHAR.
    > I haven't seen the case "fixed-length and may have trailing space
    > characters" yet.
    >
    > Regards,
    > Jo[/ref]

    yes char(7) will also accept values fewer than 7 charachters, and
    therefor it has no use for me, my purpose is to stop people from
    mistakenly register fewer characters

    --


    Thanks in advance
    Kherse Sefid
    kherse Guest

  11. #11

    Default Re: fix lenght

    On Apr 19, 11:31 am, kherse sefid <com> wrote: [/ref]




    >
    > yes char(7) will also accept values fewer than 7 charachters, and
    > therefor it has no use for me, my purpose is to stop people from
    > mistakenly register fewer characters
    >
    > --
    >
    > Thanks in advance
    > Kherse Sefid[/ref]

    How do people register this information? A common way to handle this
    sort of thing is through the user interface, e.g web-based forms. It's
    easy in PHP, for instance, to set up these kind of constraints.

    strawberry Guest

Similar Threads

  1. Lenght of NText field
    By PHRED-SE in forum Coldfusion Database Access
    Replies: 1
    Last Post: June 11th, 10:56 PM
  2. 3 colums in equal lenght
    By Procor in forum PHP Development
    Replies: 3
    Last Post: April 17th, 08:04 PM
  3. Array lenght out of range
    By Angelo_Marfisi@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 1
    Last Post: August 4th, 01:35 PM
  4. Wave file lenght
    By Eddie in forum UNIX Programming
    Replies: 4
    Last Post: September 10th, 10:07 PM
  5. how to limit paragraph lenght
    By gepeto in forum Macromedia Freehand
    Replies: 3
    Last Post: September 8th, 03:46 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