Exact length column in SQL Server

Question

Is there a way to make a column have a contraint of exactly so many characters? I have a string of 152 characters, and want the column to only accept values that are 152 in length, not 151, not 153. I know char can handle the overflow, but what about the minimum version?

Dualism

Char would also handle the minimum version, no? It is a fixed width column. Trim(), Ltrim(), Rtrim() and checking length is not necessary (else you need to consider all kinds of whitespaces to be trimmed, and also those that might be within - ie: what is the length of "hello__________there" - underscores as spaces ?). – Submaxillary

No it adds whitespace to the end of the column even if the actual text is less then 152 character. – Dualism

So what? Isn't space part of a data? What if the spaces are not leading or trailing but included in text like "hello____________there"? Also it is not only spaces that could be leading or trailing yet they look like "spaces". Then you should have some kind of function to check the strict rules IMHO. – Submaxillary

What do you mean? With len(trim([col])) = 152 I can still paste text that is shorter than 152 not considering the spaces. ie: simply put this is in fact shorter than 10 (assume underscore is space): "______long" – Submaxillary

My question is simple, what about leading and spaces contained in the text? What about characters like a space but not taken care of with a simple TRIM()? - Like a TAB character – Submaxillary

There will NEVER be any. This is a 152 character length token bud – Dualism

Answer 1

Add a check constraint which asserts that the length of the incoming string is exactly 152 characters:

ALTER TABLE [dbo].[YourTable] WITH CHECK
ADD CONSTRAINT [cnstr] CHECK (LEN(LTRIM([col])) = 152);

Devotion

Just an FYI: No need for LTRIM, since LEN does that for you regardless of whether you want it or not. And, of course the RTRIM should only be there is leading spaced is supposed to be ignored. – Hebrides

Ahh but if the column is defined as CHAR you would need to use ltrim rtrim or check datalength. – Ergo

@SeanLange As the comment about yours mentions, LEN automatically will discount whitespace trailing to the right, so only LTRIM should be necessary for CHAR columns. See here for a demo. – Devotion

@Tibor Karaszi Are you sure that LEN will ignore leading spaces? I have just done some testing and it seems to count them. – Ultan

@Ultan it discounts trailing spaces as he stated. – Ergo

@Sean Lange Yes, I know, he also states that "No need for LTRIM..." which seems to imply that LEN will ignore leading spaces. This is what I am questioning. – Ultan

@TimBiegeleisen what I was saying is that if your table is defined a CHAR then LEN will always be the size of the column which is not what the OP wants. They want to make sure it has characters so you would need to use datalength. – Ergo

@Ultan - I see. i can only assume he meant no need for rtrim. – Ergo

@Sean Lange He also mentions leading spaces, the comment is a bit confusing tbh. – Ultan

My bad, I got them mixed up. No need for RTRIM. But LTRIM is still needed, if leading spaced should be ignored. And the data type doesn't matter, same applies for both CHAR and VARCHAR. – Hebrides