5

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?

Mike Flynn
  • 22,342
  • 54
  • 182
  • 341
  • 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 ?). – Cetin Basoz Oct 02 '18 at 14:01
  • No it adds whitespace to the end of the column even if the actual text is less then 152 character. – Mike Flynn Oct 07 '18 at 20:37
  • 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. – Cetin Basoz Oct 07 '18 at 20:40
  • 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" – Cetin Basoz Oct 07 '18 at 20:43
  • 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 – Cetin Basoz Oct 07 '18 at 20:45
  • There will NEVER be any. This is a 152 character length token bud – Mike Flynn Oct 08 '18 at 01:54

1 Answers1

8

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);
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • 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. – Tibor Karaszi Oct 02 '18 at 13:44
  • Ahh but if the column is defined as CHAR you would need to use ltrim rtrim or check datalength. – Sean Lange Oct 02 '18 at 13:48
  • 2
    @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](http://rextester.com/DWLI18062) for a demo. – Tim Biegeleisen Oct 02 '18 at 13:50
  • @Tibor Karaszi Are you sure that LEN will ignore leading spaces? I have just done some testing and it seems to count them. – MJH Oct 02 '18 at 13:52
  • @MJH it discounts trailing spaces as he stated. – Sean Lange Oct 02 '18 at 13:54
  • @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. – MJH Oct 02 '18 at 13:56
  • @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. – Sean Lange Oct 02 '18 at 13:56
  • @MJH - I see. i can only assume he meant no need for rtrim. – Sean Lange Oct 02 '18 at 13:56
  • @Sean Lange He also mentions leading spaces, the comment is a bit confusing tbh. – MJH Oct 02 '18 at 14:04
  • 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. – Tibor Karaszi Oct 03 '18 at 12:30