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 sefid" <kherse_sefid@yahoo.com> wrote in message
news:f04ods$52g$1@readme.uio.no...
> 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






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 ;

On 18.04.2007 13:18, Sean wrote:
> "kherse sefid" <kherse_sefid@yahoo.com> wrote in message
> news:f04ods$52g$1@readme.uio.no...
>> 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
>
>
>
>
>
>
> 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 ;
>
>
>

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 sefid" <kherse_sefid@yahoo.com> wrote in message
news:f053mo$av3$1@readme.uio.no...
> On 18.04.2007 13:18, Sean wrote:
>> "kherse sefid" <kherse_sefid@yahoo.com> wrote in message
>> news:f04ods$52g$1@readme.uio.no...
>>> 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
>>
>>
>>
>>
>>
>>
>> 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 ;
>>
>>
>>
>
> thanks, it's great, but is there any easy way (like a datatype) we can use
> for this purpose?
>
> --
>
>
> Thanks in advance
> Kherse Sefid


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.

kherse sefid schrieb:
> 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?

Use CHAR.

On Apr 18, 5:50 pm, Joachim Durchholz <j...@durchholz.org> wrote:
> kherse sefid schrieb:
>
> > 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?
>
> Use CHAR.

What will that achieve?

strawberry schrieb:
> On Apr 18, 5:50 pm, Joachim Durchholz <j...@durchholz.org> wrote:
>> Use CHAR.
>
> What will that achieve?

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

On Apr 18, 8:29 pm, Joachim Durchholz <j...@durchholz.org> wrote:
> strawberry schrieb:
>
> > On Apr 18, 5:50 pm, Joachim Durchholz <j...@durchholz.org> wrote:
> >> Use CHAR.
>
> > What will that achieve?
>
> 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


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

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

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

On 19.04.2007 09:42, Joachim Durchholz wrote:
> strawberry schrieb:
>> But CHAR(7) will accept values of fewer than 7 characters - or am I
>> missing something?
>
> 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

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