Why does my fields have white space after the data? 1

[mrgulic]

I am using SQL 2005.
Microsoft SQL Server Management Studio 9.00.1399.00

I just started noticing that the data in my fields had trailing white space in it equaling the fieds size less the data.

Example:
"SAN/NAS "

The field type/size is: nchar(25)

Even when I do the following the resulting data still has the while space:

UPDATE [cmdb].[dbo].[device_type]
   SET [device_type_name] = 'SAN/NAS'
 WHERE [device_type_id] = '4'

I am new to programming and very new to sql. I noticed this while binding the table to a listbox control.

ddl_deviceType.DataSource = dbu.GetSqlDataReader(sqlDatasource, sqlCatalog, sqlSecurity, sqlSelect);
ddl_deviceType.DataValueField = "device_type_id";
ddl_deviceType.DataTextField = "device_type_name";
ddl_deviceType.DataBind();
ddl_deviceType.Items.Insert(0, new ListItem("- Select Device Type -"));

thanks in advance for your insight.

 

[gmmastros]

Char and nchar are both padded with spaces. This is by design.

If you want to permanently remove the spaces, then you should change the data type to nvarchar.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mrgulic]

that didnt work, but it did give me the information (padding) to do an informed search on MSDN and i found this.

SET ANSI_PADDING is always ON for nchar and nvarchar. SET ANSI_PADDING OFF does not apply to the nchar or nvarchar data types.

 

[SQLSister]

Once you convert to nvarchar, you have to update the field to trim the spaces.

update my table 
set myfield = ltrim(rtrim(myfield))

Never use char unless the data contained in it will always be the same length. For instance, StateAbbreviation is a good candidate for char datatype, StateName is not.

"NOTHING is more important in a database than integrity." ESquared