NULL and empty string

[Christian Schmidt]

Hi there,
How can I force SQL Server to store empty strings (datalength = 0) as
NULL-values? The Problem is, that my front-end often uses "IF
IsNull([Field]) then ..."
Can I only do it with an update, insert trigger, that sets each field with
an empty string to NULL? How must this trigger look like?

Greetings
Christian

[Robert E Sacks]

use the NULLIF function (e.g., NULLIF(RTRIM(@var),'') )

Christian Schmidt <uo...@rz.uni-karlsruhe.de> wrote in message
news:80clf4$dgr$1...@news.rz.uni-karlsruhe.de...

[LOBBY-DATA-BANK]

Set the default value for the fields to .NULL. Remember that bit fields
can't be .NULL.

Good luck.

Gabriel Lozano
Developer
LOBBY-DATA-BANK

Christian Schmidt wrote in message
<80clf4$dgr$1...@news.rz.uni-karlsruhe.de>...

[BPMargolin]

Gabriel,

You are correct that bit fields can not be NULL in SQL Server 6.x., however
SQL Server 7.0 does permit bit fields to be NULLable. There is a bug in SS7
Enterprise Manager that will not present this option, however one can set
bit fields to NULL if one uses CREATE TABLE statements via Query Analyzer.

LOBBY-DATA-BANK <l...@online.be> wrote in message
news:80efu7$svv$1...@trex.antw.online.be...

[Christian Schmidt]

Hi Gabriel,

I've tried, but when I delete the entry of a nvarchar-field in EM and leave
the field, it contains an empty string, not NULL. The fields are only
initialised with NULL (no matter if DEFAULT NULL). I'm using SQL Server 7.0
(without SP)
Do I really have to use a trigger, that scans all fields and sets all empty
strings to NULL (by NULLIF as Robert suggests) ? If so, how can I scan all
the fields of the inserted table?

[Gleb Zabezhinskiy]

Would it be easier just put "IF
IsNull([Field]) or trim([Field])="" then ..."?

[Gina Hagg]

Here's a simple example.

CREATE TRIGGER setstringtonull ON [ginatest]
FOR INSERT, UPDATE
AS
declare @stringfield varchar(20),@myid int
select @myid= myid,@stringfield = myname from inserted

if @stringfield = ''
update ginatest set myname = NULL where
MyId = @myid

[Kirk A . Viehland]

IF your front end is using VB you can have your devolper change there
integrity check to look like this.

IF IsNull([Field]) Then
strSQL = Update [Table] Set [Field] = vbNull where CriteriaIS
End if

Similar functionality could be achieved in C++. The Null charcter is ASCII
code 0.

Your front-end should not be updating a null containing field.
LOGIC:
For i = 1 to Fields.count
IF IsNull(Field(i)) Then
'CREATE UPDATE THAT DOESN'T REFERENCE FEILD.
Else
'UPDATE WITH NEW FIELD VALUE.
End if
Next i

Remeber: Developers should always be kind to there DBA.

Hope this helps.

Kirk A. Viehland

Gleb Zabezhinskiy <gl...@scotland.ho.att.com> wrote in message
news:80h8vi$kn...@kcweb01.netnews.att.com...

> Would it be easier just put "IF
> IsNull([Field]) or trim([Field])="" then ..."?
>
> Christian Schmidt wrote in message
> <80clf4$dgr$1...@news.rz.uni-karlsruhe.de>...