varchar minimum length check

[MikeOS]

Hi,

I have a column set to varchar(12) so I can ensure that the length of the string entered will never be more than 12 characters but I want to limit the string to a minimum of 8 characters, so I end up with a string that is from 8-12 characters long. How do I ensure the minimum length of 8 characters? I'm using SQL Server 2005 if that helps. I tried adding a check constraint like so:

DATALENGTH(UserName) >= 8

But I keep getting an error when I save the table, so any help would be very much appreciated.

Thanks

[felgall]

The database doesn't provide a way to enforce minimum length. You would have to validate the data before puting it in the database to check for that.

[mattyblah]

The database doesn't provide a way to enforce minimum length. You would have to validate the data before puting it in the database to check for that.

SQL Server 2005 fully supports check constraints that would enforce a minimum character length so if you are not familiar with the RDBMS please don't misinform the OP.

To the OP, hopefully the following SQL will help:

create table #t
(
col1 varchar(12) check (len(col1) >= 8)
)

insert into #t values ('1234567')

You should see that it will give you an insert check constraint error.

[MikeOS]

Thanks mattyblah, worked perfectly.