By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,244 Members | 1,972 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,244 IT Pros & Developers. It's quick & easy.

Specify Allow Zero Length String for Field

NeoPa
Expert Mod 15k+
P: 31,433
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.
Mar 19 '15 #1

✓ answered by Rabbit

Expand|Select|Wrap|Line Numbers
  1. ALTER TABLE [schema_name].[table_name]
  2. ADD CONSTRAINT [constraint_name]
  3. 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().

Share this Question
Share on Google+
4 Replies


Rabbit
Expert Mod 10K+
P: 12,359
Expand|Select|Wrap|Line Numbers
  1. ALTER TABLE [schema_name].[table_name]
  2. ADD CONSTRAINT [constraint_name]
  3. 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().
Mar 19 '15 #2

NeoPa
Expert Mod 15k+
P: 31,433
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.
Mar 20 '15 #3

Rabbit
Expert Mod 10K+
P: 12,359
I don't know of a check box option for ZLS like there is in Access
Mar 20 '15 #4

NeoPa
Expert Mod 15k+
P: 31,433
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.
Mar 20 '15 #5

Post your reply

Sign in to post your reply or Sign up for a free account.