Board index » delphi » Empty Field and NULL Field

Empty Field and NULL Field

Hi,

How can I make  MSSQL 7 to consider an empty field ('') equivalent as a
NULL field. I've heard that for Oracle, they are the same?

I must always add  to a sql statement like:

/select * from Table where (Field is Null )/
a...
/ or (Field='')/

Thanx

 

Re:Empty Field and NULL Field


Hi Lamon,

You must to create a Constraint DEFAULT   for the column in the table.
Specifies the value that will be provided for the column when a
value has not been supplied explicitly in an INSERT  statement.

Regards,

Jean-Pierre Lamon escreveu na mensagem <37E795D3.ACDDE...@est-stf.ch>...

Quote
>Hi,

>How can I make  MSSQL 7 to consider an empty field ('') equivalent as a
>NULL field. I've heard that for Oracle, they are the same?

>I must always add  to a sql statement like:

>/select * from Table where (Field is Null )/
>a...
>/ or (Field='')/

>Thanx

Re:Empty Field and NULL Field


Hi Luiz,

Thanx for your response. But for inserted records, no problem, the default
value is NULL. But my problem comes when a field is changed from something
to... nothing (emptying?) on an update.
Any idea more?

Quote
"Luiz H.Stein" wrote:
> Hi Lamon,

> You must to create a Constraint DEFAULT   for the column in the table.
> Specifies the value that will be provided for the column when a
> value has not been supplied explicitly in an INSERT  statement.

> Regards,

> Jean-Pierre Lamon escreveu na mensagem <37E795D3.ACDDE...@est-stf.ch>...
> >Hi,

> >How can I make  MSSQL 7 to consider an empty field ('') equivalent as a
> >NULL field. I've heard that for Oracle, they are the same?

> >I must always add  to a sql statement like:

> >/select * from Table where (Field is Null )/
> >a...
> >/ or (Field='')/

> >Thanx

Re:Empty Field and NULL Field


I must always add  to a sql statement like:

Quote

>/select * from Table where (Field is Null )/
>a...
>/ or (Field='')/

Yes,
If you are searching for a field that can be either Null
or ' ' , so apply (Field is Null OR Field = '') in the Where
clause.
Null is not equivalent a '  '.

Jean-Pierre Lamon escreveu na mensagem <37E795D3.ACDDE...@est-stf.ch>...

Quote
>Hi,

>How can I make  MSSQL 7 to consider an empty field ('') equivalent as a
>NULL field. I've heard that for Oracle, they are the same?

>Thanx

Re:Empty Field and NULL Field


Quote
Jean-Pierre Lamon wrote:
> How can I make  MSSQL 7 to consider an empty field ('') equivalent as a
> NULL field. I've heard that for Oracle, they are the same?

Who is putting the blank strings into the database? Is it your delphi
app, or is it something else that you have no control over? The solution
might be to alter your program.

I use an OnSetText to Clear the field when the user writes a blank
string back.

Harold Howe [TeamB]
http://www.bcbdev.com

Re:Empty Field and NULL Field


Hi,

I think the ISNULL() function could help you.  The function has the
following signature:

ISNULL(expression1, expression2)

It returns expression2 when expression1 is NULL, otherwise it returns
expression1

It could be applied to your problem as follows:

Select
*
....
Where
'' = ISNULL(aColumnName, '')

Quote
Jean-Pierre Lamon <jpierre.la...@est-stf.ch> wrote in message

news:37E795D3.ACDDEAE9@est-stf.ch...
Quote
> Hi,

> How can I make  MSSQL 7 to consider an empty field ('') equivalent as a
> NULL field. I've heard that for Oracle, they are the same?

> I must always add  to a sql statement like:

> /select * from Table where (Field is Null )/
> a...
> / or (Field='')/

> Thanx

Re:Empty Field and NULL Field


thanx David, ...works fine!

Regards

Other Threads