Trim Column Data

[johncassell]

Hello,

I am using SQL Express 2005 and would like to find out how to trim values.

If I have a field with a max of 10 chars and i enter '15' it will store it in the database as '15........' (Where . is a white space) I know there will be some code to trim it in my form but even manually entering it forces the creation of lots of white spaces after it.

Is there a standard way to force a trim?

Thanks

John

 

[cjard]

You created the column as either a CHAR or NCHAR type, which for performance reasons, always pads the contents out to the width of the field

Perhaps you should have used a VARCHAR or NVARCHAR instead

 

[johncassell]

thanks cjard, ill do that.

john

 

[Arg81]

the thing when using varchar or nvarchar (I myself am guilty) is you tend to set them up and leave at the default length of 50, even if your max is going to be 5...

Most of my fields are varchar, but after initially setting up I went through and set them to what I thought would be the "best" max to use (as it also then decreases database size).

 

[cjard]

the thing when using varchar or nvarchar (I myself am guilty) is you tend to set them up and leave at the default length of 50, even if your max is going to be 5...

Most of my fields are varchar, but after initially setting up I went through and set them to what I thought would be the "best" max to use (as it also then decreases database size).

I tend not to.. I use a visual designer for my Oracle tables that puts the length in tab-order next to the type, so I've always chosen a sensible max upon table creation. You Bad Boy!

Varchar varies. Decreasing the max shouldnt reduce the database size.. A Varchar50 with "aaa" in it should only take 3 bytes

 

[Arg81]

A Varchar50 with "aaa" in it should only take 3 bytes

True, but it's bad database design to be in that situation, and if I'm having a go at other people's designs then I must correct my sins first