x

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
more ▼

asked Aug 10, 2011 at 05:04 AM in Default

Mandar Alawani gravatar image

Mandar Alawani
376 32 37 44

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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
more ▼

answered Aug 10, 2011 at 05:12 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

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, 2011 at 05:15 AM Mandar Alawani
don't use varchar then, use char
Aug 10, 2011 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, 2011 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, 2011 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, 2011 at 06:06 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Aug 10, 2011 at 05:14 AM

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

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, 2011 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, 2011 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, 2011 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, 2011 at 06:16 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1948
x31
x22

asked: Aug 10, 2011 at 05:04 AM

Seen: 3299 times

Last Updated: Aug 10, 2011 at 05:04 AM