How to store Zero length strings, numbers, dates?

I am used to storing Null when a value does not exist. I have been told to
store "zero length strings" in some cases instead. Fine . . .
I do not know how to generate a "zero length string" or a "zero length
number" or anything else "zero length." But I suspect it is simple. Can you
provide a few simple examples?
Thanks
Michaelset columnName = '' (that's 2 single quotes)
for a zero length string.
If the column is a character data type, no problem.
If it's an Int though, the column will return a 0, not necessarily what you
want.
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:60BEB714-6B32-473A-8CD6-9036620F3892@.microsoft.com...
>I am used to storing Null when a value does not exist. I have been told to
> store "zero length strings" in some cases instead. Fine . . .
> I do not know how to generate a "zero length string" or a "zero length
> number" or anything else "zero length." But I suspect it is simple. Can
> you
> provide a few simple examples?
> Thanks
> Michael
>|||Why are you told to store zero length string? Null is a perfectly good value
especially for cases where the value is unknown.
Anyway, zero length is equivalent to "".
e.g.
create table tb(abc varchar(10) not null)
insert tb values('abc')
insert tb values('')
insert tb values('def')
-oj
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:60BEB714-6B32-473A-8CD6-9036620F3892@.microsoft.com...
>I am used to storing Null when a value does not exist. I have been told to
> store "zero length strings" in some cases instead. Fine . . .
> I do not know how to generate a "zero length string" or a "zero length
> number" or anything else "zero length." But I suspect it is simple. Can
> you
> provide a few simple examples?
> Thanks
> Michael
>|||I prefer storing Nulls, but that's me. An empty string is typically designat
ed
by two single quotes like so:
Create Table Foo
(
Bar VarChar(10)
)
Insert Foo (Bar) Values('')
There is no such thing as a "zero length number." If the value is a number,
then
it must have a numerical representation or be null. There is nothing in betw
een.
If the value is a string that has a number in it (e.g. '0' as opposed to 0),
then it is obviously the rules above apply.
Thomas
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:60BEB714-6B32-473A-8CD6-9036620F3892@.microsoft.com...
>I am used to storing Null when a value does not exist. I have been told to
> store "zero length strings" in some cases instead. Fine . . .
> I do not know how to generate a "zero length string" or a "zero length
> number" or anything else "zero length." But I suspect it is simple. Can y
ou
> provide a few simple examples?
> Thanks
> Michael
>|||Another thing:
A zero length string in a date column will also not work as you think.
"Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
news:%23T4$yKOTFHA.2680@.tk2msftngp13.phx.gbl...
> set columnName = '' (that's 2 single quotes)
> for a zero length string.
> If the column is a character data type, no problem.
> If it's an Int though, the column will return a 0, not necessarily what
> you want.
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:60BEB714-6B32-473A-8CD6-9036620F3892@.microsoft.com...
>|||Null is a perfectly good value *only* for cases where the value is unknown
:)
The OP said that he was told to store zero-length strings in "some cases
instead." This tells me that in some instances they recognize that the
value is not "unknown", but rather is is "known to be nothing".
Middle names are a perfect example. If you don't know someone's middle
name, you could use NULL to indicate it is unknown. If you know that the
person has *no* middle name, you could use '' to indicate that this person
has no middle name, and that you recognize this fact.
"oj" <nospam_ojngo@.home.com> wrote in message
news:%23$IbhMOTFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Why are you told to store zero length string? Null is a perfectly good
> value especially for cases where the value is unknown.
> Anyway, zero length is equivalent to "".
> e.g.
> create table tb(abc varchar(10) not null)
> insert tb values('abc')
> insert tb values('')
> insert tb values('def')
> --
> -oj
>
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:60BEB714-6B32-473A-8CD6-9036620F3892@.microsoft.com...
>|||He also says that they want to store zero-length strings in numeric and date
columns.
That one I really don't get.
Even if you could store it, what would a zero-length string mean in these
cases?
"Michael C#" <howsa@.boutdat.com> wrote in message
news:%235Ak%23UOTFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Null is a perfectly good value *only* for cases where the value is unknown
> :)
> The OP said that he was told to store zero-length strings in "some cases
> instead." This tells me that in some instances they recognize that the
> value is not "unknown", but rather is is "known to be nothing".
> Middle names are a perfect example. If you don't know someone's middle
> name, you could use NULL to indicate it is unknown. If you know that the
> person has *no* middle name, you could use '' to indicate that this person
> has no middle name, and that you recognize this fact.
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:%23$IbhMOTFHA.3544@.TK2MSFTNGP12.phx.gbl...
>|||Some people store dates in CHAR columns. Not something I would recommend,
but to each his/her own. The only other way around storing NULLs in numeric
and DATETIME columns would be to define a value that is out of range for
your purposes. For instance "9999-12-31" for an out of range date. Of
course then you introduce the Y10K problem which will have your great,
great, great, great, great (x 100's) grandchildren pulling out their hair
and cursing the day you were born...
"Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
news:e$MCzbOTFHA.2424@.TK2MSFTNGP09.phx.gbl...
> He also says that they want to store zero-length strings in numeric and
> date columns.
> That one I really don't get.
> Even if you could store it, what would a zero-length string mean in these
> cases?
> "Michael C#" <howsa@.boutdat.com> wrote in message
> news:%235Ak%23UOTFHA.3280@.TK2MSFTNGP09.phx.gbl...
>|||I would like to thank everyone who responded!
The individual who told me store "zero length strings" feels this "saves
space" and is "more efficient" than storing NULL. I know there are
length-indiocators and Null-indicators stored on the database page but I
don't exactly know their behaviors. My position has always been that storin
g
NULL is way less than 1% of the space normally used by data, so don't worry
about it. Am I overlooking something "material?"
Michael
"oj" wrote:

> Why are you told to store zero length string? Null is a perfectly good val
ue
> especially for cases where the value is unknown.
> Anyway, zero length is equivalent to "".
> e.g.
> create table tb(abc varchar(10) not null)
> insert tb values('abc')
> insert tb values('')
> insert tb values('def')
> --
> -oj
>
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:60BEB714-6B32-473A-8CD6-9036620F3892@.microsoft.com...
>
>|||zero-length neurons?
"Raymond D'Anjou" wrote:

> He also says that they want to store zero-length strings in numeric and da
te
> columns.
> That one I really don't get.
> Even if you could store it, what would a zero-length string mean in these
> cases?
> "Michael C#" <howsa@.boutdat.com> wrote in message
> news:%235Ak%23UOTFHA.3280@.TK2MSFTNGP09.phx.gbl...
>
>

How to store HTML code in SQL server table

Hello,
I'm creating something like a web site builder in ASP.Net, and I need to
store an HTML code in the SQL server table. If my customer just enters an
HTML code, it is stored in the SQL server table like this: <table
cellSpacing="0" cellPadding="0" width="760"
border="0">, instead of <table cellSpacing="0" cellPadding="0"
width="760" border="0">
This significantly increases the size of the stored code.
Are there any conversion functions or something like this to avoid it? Where
this conversion should be done - in ASP.Net or SQL server? How do I convert
it back to create an HTML page?
Please note that I must use the Unicode data type - ntext, nchar etc, since
I'm using some Cyrillic characters.
I would appreciate your advice very much.
Thank you,
Peter Afonin
Peter,
ntext in SQL Server will store Html markup just fine. You are encoding
your html before storing it in the database. Find out where you are
encoding the markup and change that code. Debug your code and make sure
that you are indeed sending pure html to the database. SQL Server will not
automatically encode html, so your code is doing it somewhere.
Best regards,
Jeffrey Palermo
"Peter Afonin" <pva@.speakeasy.net> wrote in message
news:eyXuQOa1EHA.3820@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I'm creating something like a web site builder in ASP.Net, and I need to
> store an HTML code in the SQL server table. If my customer just enters an
> HTML code, it is stored in the SQL server table like this: <table
> cellSpacing="0" cellPadding="0" width="760"
> border="0">, instead of <table cellSpacing="0"
cellPadding="0"
> width="760" border="0">
> This significantly increases the size of the stored code.
> Are there any conversion functions or something like this to avoid it?
Where
> this conversion should be done - in ASP.Net or SQL server? How do I
convert
> it back to create an HTML page?
> Please note that I must use the Unicode data type - ntext, nchar etc,
since
> I'm using some Cyrillic characters.
> I would appreciate your advice very much.
> Thank you,
> --
> Peter Afonin
>
|||What is the code you use to store the string you get in the SQL database?
"Peter Afonin" <pva@.speakeasy.net> a crit dans le message de
news:eyXuQOa1EHA.3820@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I'm creating something like a web site builder in ASP.Net, and I need to
> store an HTML code in the SQL server table. If my customer just enters an
> HTML code, it is stored in the SQL server table like this: <table
> cellSpacing="0" cellPadding="0" width="760"
> border="0">, instead of <table cellSpacing="0"
cellPadding="0"
> width="760" border="0">
> This significantly increases the size of the stored code.
> Are there any conversion functions or something like this to avoid it?
Where
> this conversion should be done - in ASP.Net or SQL server? How do I
convert
> it back to create an HTML page?
> Please note that I must use the Unicode data type - ntext, nchar etc,
since
> I'm using some Cyrillic characters.
> I would appreciate your advice very much.
> Thank you,
> --
> Peter Afonin
>
>
|||As the other poster states, it is not SQL that is doing the encoding, it is
something else...
Also, if the html pages will be less than 4000 unicode characters, you may
prefer to strore the data in a varchar column.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter Afonin" <pva@.speakeasy.net> wrote in message
news:eyXuQOa1EHA.3820@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I'm creating something like a web site builder in ASP.Net, and I need to
> store an HTML code in the SQL server table. If my customer just enters an
> HTML code, it is stored in the SQL server table like this: <table
> cellSpacing="0" cellPadding="0" width="760"
> border="0">, instead of <table cellSpacing="0"
cellPadding="0"
> width="760" border="0">
> This significantly increases the size of the stored code.
> Are there any conversion functions or something like this to avoid it?
Where
> this conversion should be done - in ASP.Net or SQL server? How do I
convert
> it back to create an HTML page?
> Please note that I must use the Unicode data type - ntext, nchar etc,
since
> I'm using some Cyrillic characters.
> I would appreciate your advice very much.
> Thank you,
> --
> Peter Afonin
>
|||Thank you everyone for your suggestions.
So far when I use ntext it works OK, then I use the Literal control and
Server.Decode to write the data in ASP.Net.
Peter
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eL$4fPh1EHA.4004@.tk2msftngp13.phx.gbl...
> As the other poster states, it is not SQL that is doing the encoding, it
is[vbcol=seagreen]
> something else...
> Also, if the html pages will be less than 4000 unicode characters, you may
> prefer to strore the data in a varchar column.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Peter Afonin" <pva@.speakeasy.net> wrote in message
> news:eyXuQOa1EHA.3820@.TK2MSFTNGP11.phx.gbl...
an[vbcol=seagreen]
width="760"
> cellPadding="0"
> Where
> convert
> since
>
|||Wayne,
Maybe it's just a typo, but varchar cannot be properly used with unicode
and has a maximum length of 8000 characters. nvarchar has a limit of 4000
characters and should be used for all applications for maintenance purposes
(since most applications will have to support unicode in the future).
Best regards,
Jeffrey Palermo
Blog: http://dotnetjunkies.com/weblog/jpalermo
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eL$4fPh1EHA.4004@.tk2msftngp13.phx.gbl...
> As the other poster states, it is not SQL that is doing the encoding, it
is[vbcol=seagreen]
> something else...
> Also, if the html pages will be less than 4000 unicode characters, you may
> prefer to strore the data in a varchar column.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Peter Afonin" <pva@.speakeasy.net> wrote in message
> news:eyXuQOa1EHA.3820@.TK2MSFTNGP11.phx.gbl...
an[vbcol=seagreen]
width="760"
> cellPadding="0"
> Where
> convert
> since
>
|||Peter, I'm glad you have it working now.
Best regards,
Jeffrey Palermo
Blog: http://dotnetjunkies.com/weblog/jpalermo
"Peter Afonin" <peter@.gudzon.net> wrote in message
news:OAMi7Mk1EHA.1124@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Thank you everyone for your suggestions.
> So far when I use ntext it works OK, then I use the Literal control and
> Server.Decode to write the data in ASP.Net.
> Peter
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:eL$4fPh1EHA.4004@.tk2msftngp13.phx.gbl...
> is
may[vbcol=seagreen]
to
> an
> width="760"
>

How to store HTML code in SQL server table

Hello,
I'm creating something like a web site builder in ASP.Net, and I need to
store an HTML code in the SQL server table. If my customer just enters an
HTML code, it is stored in the SQL server table like this: <table
cellSpacing="0" cellPadding="0" width="760"
border="0">, instead of <table cellSpacing="0" cellPadding="0"
width="760" border="0">
This significantly increases the size of the stored code.
Are there any conversion functions or something like this to avoid it? Where
this conversion should be done - in ASP.Net or SQL server? How do I convert
it back to create an HTML page?
Please note that I must use the Unicode data type - ntext, nchar etc, since
I'm using some Cyrillic characters.
I would appreciate your advice very much.
Thank you,
Peter AfoninPeter,
ntext in SQL Server will store Html markup just fine. You are encoding
your html before storing it in the database. Find out where you are
encoding the markup and change that code. Debug your code and make sure
that you are indeed sending pure html to the database. SQL Server will not
automatically encode html, so your code is doing it somewhere.
Best regards,
Jeffrey Palermo
"Peter Afonin" <pva@.speakeasy.net> wrote in message
news:eyXuQOa1EHA.3820@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I'm creating something like a web site builder in ASP.Net, and I need to
> store an HTML code in the SQL server table. If my customer just enters an
> HTML code, it is stored in the SQL server table like this: <table
> cellSpacing="0" cellPadding="0" width="760"
> border="0">, instead of <table cellSpacing="0"
cellPadding="0"
> width="760" border="0">
> This significantly increases the size of the stored code.
> Are there any conversion functions or something like this to avoid it?
Where
> this conversion should be done - in ASP.Net or SQL server? How do I
convert
> it back to create an HTML page?
> Please note that I must use the Unicode data type - ntext, nchar etc,
since
> I'm using some Cyrillic characters.
> I would appreciate your advice very much.
> Thank you,
> --
> Peter Afonin
>|||What is the code you use to store the string you get in the SQL database?
"Peter Afonin" <pva@.speakeasy.net> a crit dans le message de
news:eyXuQOa1EHA.3820@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I'm creating something like a web site builder in ASP.Net, and I need to
> store an HTML code in the SQL server table. If my customer just enters an
> HTML code, it is stored in the SQL server table like this: <table
> cellSpacing="0" cellPadding="0" width="760"
> border="0">, instead of <table cellSpacing="0"
cellPadding="0"
> width="760" border="0">
> This significantly increases the size of the stored code.
> Are there any conversion functions or something like this to avoid it?
Where
> this conversion should be done - in ASP.Net or SQL server? How do I
convert
> it back to create an HTML page?
> Please note that I must use the Unicode data type - ntext, nchar etc,
since
> I'm using some Cyrillic characters.
> I would appreciate your advice very much.
> Thank you,
> --
> Peter Afonin
>
>|||As the other poster states, it is not SQL that is doing the encoding, it is
something else...
Also, if the html pages will be less than 4000 unicode characters, you may
prefer to strore the data in a varchar column.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter Afonin" <pva@.speakeasy.net> wrote in message
news:eyXuQOa1EHA.3820@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I'm creating something like a web site builder in ASP.Net, and I need to
> store an HTML code in the SQL server table. If my customer just enters an
> HTML code, it is stored in the SQL server table like this: <table
> cellSpacing="0" cellPadding="0" width="760"
> border="0">, instead of <table cellSpacing="0"
cellPadding="0"
> width="760" border="0">
> This significantly increases the size of the stored code.
> Are there any conversion functions or something like this to avoid it?
Where
> this conversion should be done - in ASP.Net or SQL server? How do I
convert
> it back to create an HTML page?
> Please note that I must use the Unicode data type - ntext, nchar etc,
since
> I'm using some Cyrillic characters.
> I would appreciate your advice very much.
> Thank you,
> --
> Peter Afonin
>|||Thank you everyone for your suggestions.
So far when I use ntext it works OK, then I use the Literal control and
Server.Decode to write the data in ASP.Net.
Peter
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eL$4fPh1EHA.4004@.tk2msftngp13.phx.gbl...
> As the other poster states, it is not SQL that is doing the encoding, it
is
> something else...
> Also, if the html pages will be less than 4000 unicode characters, you may
> prefer to strore the data in a varchar column.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Peter Afonin" <pva@.speakeasy.net> wrote in message
> news:eyXuQOa1EHA.3820@.TK2MSFTNGP11.phx.gbl...
an[vbcol=seagreen]
width="760"[vbcol=seagreen]
> cellPadding="0"
> Where
> convert
> since
>|||Wayne,
Maybe it's just a typo, but varchar cannot be properly used with unicode
and has a maximum length of 8000 characters. nvarchar has a limit of 4000
characters and should be used for all applications for maintenance purposes
(since most applications will have to support unicode in the future).
Best regards,
Jeffrey Palermo
Blog: http://dotnetjunkies.com/weblog/jpalermo
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eL$4fPh1EHA.4004@.tk2msftngp13.phx.gbl...
> As the other poster states, it is not SQL that is doing the encoding, it
is
> something else...
> Also, if the html pages will be less than 4000 unicode characters, you may
> prefer to strore the data in a varchar column.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Peter Afonin" <pva@.speakeasy.net> wrote in message
> news:eyXuQOa1EHA.3820@.TK2MSFTNGP11.phx.gbl...
an[vbcol=seagreen]
width="760"[vbcol=seagreen]
> cellPadding="0"
> Where
> convert
> since
>|||Peter, I'm glad you have it working now.
Best regards,
Jeffrey Palermo
Blog: http://dotnetjunkies.com/weblog/jpalermo
"Peter Afonin" <peter@.gudzon.net> wrote in message
news:OAMi7Mk1EHA.1124@.tk2msftngp13.phx.gbl...
> Thank you everyone for your suggestions.
> So far when I use ntext it works OK, then I use the Literal control and
> Server.Decode to write the data in ASP.Net.
> Peter
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:eL$4fPh1EHA.4004@.tk2msftngp13.phx.gbl...
> is
may[vbcol=seagreen]
to[vbcol=seagreen]
> an
> width="760"
>