Empty string

MyCol is a varchar(1) column.
SELECT MyCol + '.' FROM tbl
returns ' .' (space+dot), when MyCol contains empty string.
Why? How can I make it to return '.' (with no space)?
I use SQL Server 2000, default settings.
Thanks.
First of all, why are you using varchar(1)? You're wasting an extra byte
per row for nothing... Use CHAR(1).
Second, how are you determining that the output is ' .'? Is this happening
client-side? I cannot reproduce what you're talking about, using the
following:
declare @.table table(blah varchar(1))
insert @.table values ('')
select len(blah + '.')
from @.table
Can you post code to reproduce your problem?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Vik" <viktorum@.==hotmail.com==> wrote in message
news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
> MyCol is a varchar(1) column.
> SELECT MyCol + '.' FROM tbl
> returns ' .' (space+dot), when MyCol contains empty string.
> Why? How can I make it to return '.' (with no space)?
> I use SQL Server 2000, default settings.
> Thanks.
>
|||>> Can you post code to reproduce your problem?
INSERT @.table SELECT SPACE(1) ;
Anith
|||You can use any of LTRIM, REPLACE, SUBSTRING, STUFF, CASE, RIGHT or some
other string function to get this done. See the topic String functions in
SQL Server Books Online for details.
Anith
|||> when MyCol contains empty string.
What is your definition of an empty string? Can you show a repro? Like
Adam, I can't figure out whow you're doing this, unless you have a different
definition of "empty string" than I. I couldn't yield your result unless I
insert a space.
set nocount on
set concat_null_yields_null on
set ansi_padding off
create table #t(blah varchar(1))
insert #t values (SPACE(0))
insert #t values ('')
insert #t values (SPACE(1))
insert #t values (' ')
insert #t values (NULL)
select blah+'.', len(blah + '.') from #t
drop table #t
go
set concat_null_yields_null off
set ansi_padding off
create table #t(blah varchar(1))
insert #t values (SPACE(0))
insert #t values ('')
insert #t values (SPACE(1))
insert #t values (' ')
select blah+'.', len(blah + '.') from #t
drop table #t
go
set concat_null_yields_null on
set ansi_padding on
create table #t(blah varchar(1))
insert #t values (SPACE(0))
insert #t values ('')
insert #t values (SPACE(1))
insert #t values (' ')
insert #t values (NULL)
select blah+'.', len(blah + '.') from #t
drop table #t
go
set concat_null_yields_null off
set ansi_padding on
create table #t(blah varchar(1))
insert #t values (SPACE(0))
insert #t values ('')
insert #t values (SPACE(1))
insert #t values (' ')
select blah+'.', len(blah + '.') from #t
drop table #t
go
http://www.aspfaq.com/
(Reverse address to reply.)
|||I believe the OP said it was an empty string?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uoK1udCyEHA.1956@.TK2MSFTNGP14.phx.gbl...
> INSERT @.table SELECT SPACE(1) ;
> --
> Anith
>
|||I asked for a definition of empty string. To me, that's SPACE(0), not
SPACE(1).
http://www.aspfaq.com/
(Reverse address to reply.)
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#KGvagCyEHA.3400@.TK2MSFTNGP10.phx.gbl...
> I believe the OP said it was an empty string?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:uoK1udCyEHA.1956@.TK2MSFTNGP14.phx.gbl...
>
|||It appeared that MyCol contained a space instead of empty string. What
confused me was that LEN(MyCol) returned 0, when MyCol=' ' (one space).
So, a question is why LEN(' ') returns 0 and DATALENGTH(' ') returns 1?
Thanks.
"Vik" <viktorum@.==hotmail.com==> wrote in message
news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
> MyCol is a varchar(1) column.
> SELECT MyCol + '.' FROM tbl
> returns ' .' (space+dot), when MyCol contains empty string.
> Why? How can I make it to return '.' (with no space)?
> I use SQL Server 2000, default settings.
> Thanks.
>
|||From BOL:
LEN
Returns the number of characters, rather than the number of bytes, of the
given string expression, excluding trailing blanks.
I agree, that can get confusing!
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Vik" <viktorum@.==hotmail.com==> wrote in message
news:OSjkejCyEHA.4064@.TK2MSFTNGP10.phx.gbl...
> It appeared that MyCol contained a space instead of empty string. What
> confused me was that LEN(MyCol) returned 0, when MyCol=' ' (one space).
> So, a question is why LEN(' ') returns 0 and DATALENGTH(' ') returns 1?
> Thanks.
> "Vik" <viktorum@.==hotmail.com==> wrote in message
> news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
>
|||Here is a reason to use VARCHAR(1) instead of CHAR(1): when ANSI_PADDING is
set on. CHAR(1) will store SPACE(0) as space, whereas VARCHAR(1) will store
SPACE(0) as an empty string.
Sincerely,
Anthony Thomas

"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OpZ3DaCyEHA.1452@.TK2MSFTNGP11.phx.gbl...
First of all, why are you using varchar(1)? You're wasting an extra byte
per row for nothing... Use CHAR(1).
Second, how are you determining that the output is ' .'? Is this happening
client-side? I cannot reproduce what you're talking about, using the
following:
declare @.table table(blah varchar(1))
insert @.table values ('')
select len(blah + '.')
from @.table
Can you post code to reproduce your problem?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Vik" <viktorum@.==hotmail.com==> wrote in message
news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
> MyCol is a varchar(1) column.
> SELECT MyCol + '.' FROM tbl
> returns ' .' (space+dot), when MyCol contains empty string.
> Why? How can I make it to return '.' (with no space)?
> I use SQL Server 2000, default settings.
> Thanks.
>