asked
Length of Blank spaces in column
Hi, I have a column with VARCHAR datatype in SQL server 2005 database. When I insert 3 blanks spaces (' ') in the column, the column still shows me that it is empty. It does not show me 3 spaces. Is this correct behaviour? This column value is NOT NULL as this row does not appear when I select values with column= NULL. Is my understanding correct ? If so , what can be reason of this behaviour? Thanks in advance, Mandar
If you want to select null values, you can't use the = operator, you should instead use IS NULL. SELECT bla FROM ha WHERE col IS NULL or to exclude null values SELECT bla FROM ha WHERE col IS NOT NULL SQL Server right-trims strings, so any number of trailing blanks is stored as empty string. declare @t table (id int identity(1,1), f varchar(10),f2 varchar(10)) insert into @t (f,f2) values(' .',' ') select len(f),len(f2) from @t
@fatherjack : true! Having never needed to implement a field made entirely of varying-multiple spaces, I am struggling to see the point here.....
what needs to be done if I want to store and retrieve BLANK spaces from a column..Meaning, if I have stored 4 blanks spaces in the column, I should able to select it..
I'm not sure you can retrieve the blanks at all. But if you use SET ANSI_PADDING ON when you create the column, you'll get the blanks stored, and if you do UPDATE aTable SET aColumn=aColumn + '.' you'll get the blanks followed by the dot. If the column is created with SET ANSI_PADDING OFF, you'll only get the dot in the above operation.
I apologize in advance for the silly question, but this is requirement from the customer. When we open the table in SQL Management Studio (Tablename - right click -- Open Table), he wants to know if the blank spaces inserted can be selected as this is a scenario in another database..
Varchar may 'right-trim' strings depending on your settings. From [MSDN/BOL][1] > The varchar data type is a variable-length data type. Values shorter than the size of the column are not right-padded to the size of the column. If the `ANSI_PADDING` option was set to OFF when the column was created, any trailing blanks are truncated from character values stored in the column. If `ANSI_PADDING` was set ON when the column was created, trailing blanks are not truncated. [1]:
http://msdn.microsoft.com/en-us/library/ms175055.aspx
@Kev Riley - any comparison right trims the strings before comparing them, so it's really not that easy to get to the blanks. set ansi_padding on create table #t (id int identity(1,1), f varchar(10)) insert into #t (f) values(' ') select * from #t where f'' drop table #t and set ansi_padding on create table #t (id int identity(1,1), f varchar(10)) insert into #t (f) values(' ') select * from #t where f' ' drop table #t Two examples of how strings are trimmed on comparison.
the below two gives the same result for me (SQL Server 2005) set ansi_padding on create table #t (id int identity(1,1), f varchar(10),f2 varchar(10)) insert into #t (f,f2) values(' .',' ') select len(f),len(f2) from #t drop table #t set ansi_padding off create table #t (id int identity(1,1), f varchar(10),f2 varchar(10)) insert into #t (f,f2) values(' .',' ') select len(f),len(f2) from #t drop table #t Ergo: ANSI_PADDING controls how it's stored, not how it's presented or compared.
@Magnus : LEN() Returns the number of characters of the specified string expression, excluding trailing blanks. Use DATALENGTH() to get the 'byte length'
correct, one way would be to add delimiters set ansi_padding on create table #t (id int identity(1,1), f varchar(10)) insert into #t (f) values(' ') select * from #t where '#'+f+'#' '#'+' '+'#' drop table #t go set ansi_padding off create table #t (id int identity(1,1), f varchar(10)) insert into #t (f) values(' ') select * from #t where '#'+f+'#' '#'+' '+'#' drop table #t`
