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 this string

Hello,
I have string that I need to store in a database, but I don't know exactly
how. These strings could look like this:
A001111105110416352489
I parse the string through a RegEx looking like this:
^([A])(\d{3})([0|1]{3})([0|1])(\w*)
A = Mode, must be A in this case
001 = Component address
111 = 3-button status, can be 000, 001, 010, 100, 101, 110, 011 etc...
0 = Can be 0 or 1
05110416352489 = If above is 1, then this is a barcode, otherwise this isn't
sent.
Currently I store the above values in a field each. But the problem is that
I would like to store each button status separately and there might be
4-buttons, 5-buttons and so on in the future. I don't believe it's a good
idea to store each digit as a new field, but is there another way?
Here's the table:
CREATE TABLE [Actions](
[action_id] [int] IDENTITY(1,1) NOT NULL,
[component_address] [nvarchar](10) NOT NULL,
[time_stamp] [datetime] NOT NULL,
[barcode] [nvarchar](50) NULL,
[action_mode] [nvarchar](1) NOT NULL,
[button_status] [nvarchar](10) NULL,
CONSTRAINT [PK_Actions] PRIMARY KEY CLUSTERED
(
[action_id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
Thanks!
Cheers,
JonahI don't really understand what the button status thing represents in the
"real world", so it's hard to say whether storing them seperately is a
good idea or a bad idea. But if you do store the combined status of the
button(s) in a single column, you could have user defined functions to
pull out individual states (and add them to the table as computed
columns, or just use them when you need them).|||Sorry, I noticed that as well when reading my question again...
The 3-button status represents a component in the real-world system with
three buttons on it. When a button is pushed (say the 3rd one), it reports
001. When released 000. If I push the first, keeps it down and press the
third button, it reports 101, and when releasing them both 100 (or 001
depending on what was released first) and finally 000.
Since we track the time for each action on the component, I would sometimes
like to know for how long certain buttons has been pushed. I would also like
to pull statistics for a separate button (say the 2nd one) - how often is it
pushed, what time of the day etc.
I hope this gives some information about the real-world representation.
Cheers,
Jonah|||Are Defined Functions the deal here?
/Jonah
"Jonah Olsson" <jonah@.IHATESPAM.com> skrev i meddelandet
news:uiW%23NEvhGHA.2456@.TK2MSFTNGP04.phx.gbl...
> Sorry, I noticed that as well when reading my question again...
> The 3-button status represents a component in the real-world system with
> three buttons on it. When a button is pushed (say the 3rd one), it reports
> 001. When released 000. If I push the first, keeps it down and press the
> third button, it reports 101, and when releasing them both 100 (or 001
> depending on what was released first) and finally 000.
> Since we track the time for each action on the component, I would
> sometimes like to know for how long certain buttons has been pushed. I
> would also like to pull statistics for a separate button (say the 2nd
> one) - how often is it pushed, what time of the day etc.
> I hope this gives some information about the real-world representation.
> Cheers,
> Jonah
>|||Jonah Olsson (jonah@.IHATESPAM.com) writes:
> Currently I store the above values in a field each. But the problem is
> that I would like to store each button status separately and there might
> be 4-buttons, 5-buttons and so on in the future. I don't believe it's a
> good idea to store each digit as a new field, but is there another way?
> Here's the table:
> CREATE TABLE [Actions](
> [action_id] [int] IDENTITY(1,1) NOT NULL,
> [component_address] [nvarchar](10) NOT NULL,
> [time_stamp] [datetime] NOT NULL,
> [barcode] [nvarchar](50) NULL,
> [action_mode] [nvarchar](1) NOT NULL,
> [button_status] [nvarchar](10) NULL,
> CONSTRAINT [PK_Actions] PRIMARY KEY CLUSTERED
> (
> [action_id] ASC
> ) ON [PRIMARY]
> ) ON [PRIMARY]
The normal way would be to replace button_status with
CREATE TABLE ButtonStatus (
action_id int NOT NULL,
button_no tinyint NOT NULL,
button_status bit NOT NULL,
CONSTRAINT pk_ButtonStatus PRIMARY KEY(action_id, button_no),
CONSTRIANT fk_ButtonStatus_action FOREIGH KEY (action_id)
REFERENCES Atcions(actio_id)
)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> The normal way would be to replace button_status with
> CREATE TABLE ButtonStatus (
> action_id int NOT NULL,
> button_no tinyint NOT NULL,
> button_status bit NOT NULL,
> CONSTRAINT pk_ButtonStatus PRIMARY KEY(action_id, button_no),
> CONSTRIANT fk_ButtonStatus_action FOREIGH KEY (action_id)
> REFERENCES Atcions(actio_id)
> )
Ah, good one! So button_no is the button "position" from left to right? 0 1
2?
/Jonah|||Jonah Olsson (jonah@.IHATESPAM.com) writes:
> Ah, good one! So button_no is the button "position" from left to right? 0
> 1 2?
Yes. As for 0-2 vs. 1-3 or right/left vs. left/right, that is up to you.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Jonah Olsson wrote:

> Currently I store the above values in a field each. But the problem is tha
t
> I would like to store each button status separately and there might be
> 4-buttons, 5-buttons and so on in the future. I don't believe it's a good
> idea to store each digit as a new field, but is there another way?
If you can be reasonably confident that there will never be more than
31 buttons, use an integer, and create defined functions that pull out
individual button statuses from an integer.
This would be a more compact representation than the fully normalized
representation that Erland suggested; his is better in some ways though
-- it would be straightforward to search by button number, for example
("find me a time when button 1 was pressed within 3 seconds of button
2, but button 4 hadn't been pressed in the previous 60 seconds and
button 3 wasn't pressed in the following 120 seconds"). That sort of
query would be pretty impossible *without* full normalization.|||Great! Thank you both.
Cheers,
Jonah