February 4, 2002 at 9:44 am
Hi ,
Can any one tell me if there is a way to tell sql server that 1 space != 2 spaces ?
I try this in isqlw :
create table T
( PK varchar (5) NOT NULL primary key )
Insert Into T Values(' ')
Insert Into T Values(' ')
and i get :
Violation of PRIMARY KEY constraint 'PK__T__3EDDEA9E'. Cannot insert duplicate key in object 'T'.
The statement has been terminated.
Why ????
Thank you .
Tzahi .
February 4, 2002 at 10:03 am
No way that I know of. I tried changing to a CHAR and the same results. Is there a reason you need this? Trailing spaces usually are not counted in most systems or applications. In fact, they are like case sensitivty and add unnecessary complexity for the users. Can you use another character, like an _ to take the place and remove them with the application?
Steve Jones
February 4, 2002 at 3:03 pm
Actually, SQL does know the difference between 1 and 2 spaces in a varchar, except they must occurr together before a non-space character (' A')or between 2 non-space characters such as follows: 'A B' and 'A B' are different. However in varchar any spaces at the end of a word will auto right-trim (Such as 'ab ' will store as 'ab'). This is an effect of the varchar datatype in storage. Where as char(5) will auto add 2 spaces to the end in say the word add (Stored as 'add '). The key is the end spaces.
February 4, 2002 at 3:08 pm
I tried this with a CHAR column and it still is a PK violation. Apparently there is no difference.
Steve Jones
February 4, 2002 at 3:11 pm
That is correct as I said. Columns of char datatype will automatically add to the end of the data the number of space to completely fill the lenght, thus enter A + 1 space in a char(5) field will store as A + 4 spaces.
February 4, 2002 at 3:14 pm
February 5, 2002 at 2:00 am
Thank you Steve .
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply