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.
>|||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|||>> Can you post code to reproduce your problem?
INSERT @.table SELECT SPACE(1) ;
--
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...
> >> Can you post code to reproduce your problem?
> 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...
> > >> Can you post code to reproduce your problem?
> >
> > INSERT @.table SELECT SPACE(1) ;
> >
> > --
> > Anith
> >
> >
>|||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...
> > 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.
> >
> >
>|||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.
>|||"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).
>
Originally MyCol was Char(1) and contained Nulls. Then it appeared that this
column should be used in a join, so I had to get rid of Nulls.
I also have a few Web pages (in ASP.NET) built in assumption that MyCol is
not blank. So, I decided to use Varchar(1) and an empty string for MyCol
instead of using a space and updating the queries or code with a Trim
function.
> 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.
> >
> >
>|||Is your database set to a compatibility level of 65, perhaps? SQL
Server 6.5 could not store an empty string, if I recall correctly.
Otherwise, how do you know MyCol is empty? This behavior will result if
MyCol contains the value ' '. You can try rtrim(MyCol) to trim any
trailing spaces, but it would be best to find out what is going on.
Steve Kass
Drew University
Vik wrote:
>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.
>
>|||Vik wrote:
> 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.
Use DATALENGTH() for the real stored length.
create table #testing(col1 varchar(1))
insert into #testing values (space(1))
select len(col1) as 'len', datalength(col1) as 'datalength'
from #testing
len datalength
-- --
0 1
David Gugick
Imceda Software
www.imceda.com