Length of Blank spaces in column

Question

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

Answer 1

0

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

answered Aug 10 '11 at 05:12 AM

Magnus Ahlkvist
13.7k ● 13 ● 17 ● 30

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..

Aug 10 '11 at 05:15 AM Mandar Alawani

don't use varchar then, use char

Aug 10 '11 at 05:28 AM Kev Riley ♦♦

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.

Aug 10 '11 at 05:29 AM Magnus Ahlkvist

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..

Aug 10 '11 at 05:36 AM Mandar Alawani

@fatherjack : true! Having never needed to implement a field made entirely of varying-multiple spaces, I am struggling to see the point here.....

Aug 10 '11 at 06:06 AM Kev Riley ♦♦

(comments are locked)

Answer 2

0

Varchar may 'right-trim' strings depending on your settings.

From MSDN/BOL

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.

answered Aug 10 '11 at 05:14 AM

Kev Riley ♦♦
46.1k ● 38 ● 43 ● 69

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.

Aug 10 '11 at 05:19 AM Magnus Ahlkvist

@Magnus : LEN() Returns the number of characters of the specified string expression, excluding trailing blanks. Use DATALENGTH() to get the 'byte length'

Aug 10 '11 at 05:36 AM Kev Riley ♦♦

@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.

Aug 10 '11 at 06:10 AM Magnus Ahlkvist

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`

Aug 10 '11 at 06:16 AM Kev Riley ♦♦

(comments are locked)

Length of Blank spaces in column

Follow this question

Related Questions