Check Constraints example

[csb913]

I need to rtrim() a primary key every time a record is inserted. I tried to make a check constraint like :
@field=rtirm(@field)
and while it did not give me an error it is not trimming my data. The type is Nvarchar. I also tried a rule and custom datatype...The bottom line is I need to make sure that all the data is Rtrim() as it is added. How do I do this?

 

[essa2000]

Dear ;

I created a table to check your case and I think it will work for you.

Create table TestTrim
(
PrimaryID [nvarchar] (20) ,
data [varchar] (20)
CONSTRAINT [PK_TestTrim] PRIMARY KEY CLUSTERED
(
[PrimaryID]
) ON [PRIMARY]
) ON [PRIMARY]



Go to Check Contraints tab and created following constraint

([PrimaryID] = rtrim([PrimaryID]))

Then inserted following record spaces in Primary key:

Insert into TestTrim Select 'Essa ' , 'Mughal'

Run this query to check the length of that primary key it gave me 4 which shows that PrimaryID feild has been trimed by that constraint.

Select Len(PrimaryID) from TestTrim

Please , check that you should have defined lenght of nvarchar() datatype for your primary key.

Regards,
essa2000

 

[csb913]

Thank you Eessa2000 for your reply.
Everything works as you say BUT it does not really work. Using your data:
Insert into TestTrim Select 'Essaprimaryid12345 ' , 'Mughal'
Then:
Select Len(PrimaryID) from TestTrim
should get 18
Then:
select primaryID from testtrim
Notice the two space on the end...
Then:
update testtrim set primaryid=rtrim(primaryid)
Now:
select primaryid from testtrim
Notice the two spaces are gone!
In fact if you remove the check constraint you get exactly the same results!
FWIW this is not just an oddity in the query analyzer, I have several millon records that are tied to an Autodesk Mapguide application and I could not get them to 'talk' until I figured out that my primary key in SQL needed to be Rtrim() so that was easy enough, but now every time I insert a record I need to rtrim() the primaryid and I don't know how to do it...
tia
-curtis

 

[bperry]

Without any testing or research, I am guessing that this cannot be done with a Check constraint. i.e. the Check will validate an entry to make sure it passes an acceptable edit, but does not actually change the incoming entry itself.

[/i]....but now every time I insert a record... [/i]
Can this Insert process be modified to trim any trailing spaces. i.e. in other words, trim off the blanks before passing it to the insert command.

 

[csb913]

The short answer is no. The record is being added by a Visual Foxpro Remote View and trimming that data before appending the record does not seem to change the data. I could rewrite the apps that add the data, but I would rather not undertake that job right now. If I can't use a check constraint, what can I do? I am rather new at SQL and don't know the best why to accomplish this.
tia
-curtis

 

[tlbroadbent]

The following applies to SQL Server 2000.

1) The ANSI_PADDING setting controls how trailing spaces are handled. With ANSI_PADDING ON SQL does not trim trailing spaces. See "SET ANSI_PADDING" in SQL BOL.

2) "nchar, nvarchar, and ntext columns always display the SET ANSI_PADDING ON behavior, regardless of the current setting of SET ANSI_PADDING. Therefore, SQL Server does not trim trailing spaces on nvarchar columns." (Source: SQL BOL)

3) When ANSI_PADDING ON, SQL Server will not add trailing spaces to varchar columns. It does not add trailing spaces to nvarchar columns regardless of the ANSI_PADDING setting.

4) "The SQL Server ODBC driver and Microsoft OLE DB Provider for SQL Server automatically set ANSI_PADDING to ON when connecting. This can be configured in ODBC data sources, in ODBC connection attributes, or OLE DB connection properties set in the application before connecting." (Source: SQL BOL)

4) I'm not familiar with FoxPro. In VB, if a variable is DIMmed with a fixed length and that variable is used to insert data into a column trailing spaces will be added. Trimming does not remove the spaces. The variable must be DIMmed as variable length to allow trimming of trailing spaces.

Example: variable length string
DIM strTest As String

Example: fixed length string (20 characters)
DIM strTest As String * 20

Does FoxPro allow fixed and variable length strings? If it does, you should change fixed length strings to variable length so trimming can occur without a lot of additional coding. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.