Specify Allow Zero Length String for Field

[NeoPa]

I've searched around for help on this but everything I find seems to be a discussion about the relative benefits of using, or not using, this setting.

I want to specify in SQL Server that ZLSs are not acceptable in the field whether or not NULLs are allowed. If ever I have to deal with an empty-looking value I want to know it's NULL rather than a ZLS.

The version of SQL Server is 2012 R2 Express (11.0.2100.60).

Any help on this is greatly appreciated.

[Rabbit]

ALTER TABLE [schema_name].[table_name]
ADD CONSTRAINT [constraint_name]
CHECK (LEN([field_name]) > 0 OR [field_name] IS NULL)

If by zero length, you mean count trailing spaces as part of the length, use DATALENGTH(). If you don't want to count trailing spaces as part of the length, use LEN().

[NeoPa]

Thanks Rabbit :-)

I probably should have included the version in the question so I'll fix that.

I can make do with the SQL as I'll mainly be creating scripts to set up the tables anyway, but if you know, I'd certainly be interested to know how to do this from within Management Studio too.

Just to be clear - I am looking for a setting which maps across to Access as the property named "Allow Zero Length". If it isn't possible then that's weird but I can deal with it. I possibly should have written the original question more clearly. It's certainly helpful to know I can design a simple constraint for it/them.

[Rabbit]

I don't know of a check box option for ZLS like there is in Access

[NeoPa]

Cheers.

I'll experiment with whether or not the constraint causes Access to recognise it as Allow Zero Length or not. At least I know what limitations I'm working within. Thanks for the help.