How to set sql server setting let to set null value when a empty string (zero length)

How to set sql server setting let to set null value when a empty string
(zero length) set to column?
I want it automatic to set null value when maintain data is a zero-length
string press to column of table. for example,
when a statement as "Insert into table1 values ('abc', '', '')' become to
save abc, null and null three values to table1.There is no such setting in SQL Server. You could create a trigger which per
forms this, but I would
look for some alternate options instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ABC" <abc@.abc.com> wrote in message news:%230Fav01RGHA.2276@.tk2msftngp13.phx.gbl...darkred">
> How to set sql server setting let to set null value when a empty string (z
ero length) set to
> column?
> I want it automatic to set null value when maintain data is a zero-length
string press to column
> of table. for example,
> when a statement as "Insert into table1 values ('abc', '', '')' become to
save abc, null and null
> three values to table1.
>|||Would probaby have to create a trigger of the form (completely untested!)
CREATE TRIGGER NullifySomeEmptyStrings
ON table1
FOR AFTER INSERT, UPDATE
AS
--NULL out column2 if it now contains an empty string ''
IF UPDATE(column2)
BEGIN
UPDATE table1
SET column2 = NULL
WHERE table1.ThePrimaryKeyColumn IN
(SELECT ThePrimaryKeyColumn
FROM INSERTED
WHERE INSERTED.column2 = '')
END
"ABC" <abc@.abc.com> wrote in message
news:%230Fav01RGHA.2276@.tk2msftngp13.phx.gbl...
> How to set sql server setting let to set null value when a empty string
> (zero length) set to column?
> I want it automatic to set null value when maintain data is a zero-length
> string press to column of table. for example,
> when a statement as "Insert into table1 values ('abc', '', '')' become to
> save abc, null and null three values to table1.
>|||Why do you want it to be Null?
If you use Front End application, validate this and send valid data to
server
Madhivanan

How to set empty a space from a derived column expression?

hi,

This field comes from a flat file and sometimes own zero and sometimes own a empty position (after 'EUR'):

2006053000499236000005307700108287457080200408287452006052953990000000010000EUR
2006053000499236004414989200101423426004400501423422006052953990000000010000EUR0

[Column 12] == "0" ? [Column 12] : ?

TIA

What is the data type of [Column 12]?

-Jamie

|||

At destination?

[char](1) COLLATE Modern_Spanish_CI_AS NULL,

|||

No. In the pipeline.

If it is DT_STR then your expression will be:

[Column 12] == "0" ? [Column 12] : NULL(DT_STR,1,1252)

If it is DT_WSTR then it'll be

[Column 12] == "0" ? [Column 12] : NULL(DT_WSTR,1)

-Jamie

|||

I've got string [DT_STR] as datatype in my flat file definition and string [DT_WSTR] as datatype column on the right 'Expression' field (Derived Column)

I've allocated as you said this line:

[Column 12] == "0" ? [Column 12] : NULL(DT_WSTR,1)

And remains in black, ok but when I launch the package appears this:

Error at Camara Recibida [OLE DB Destination [9902]]: Columns "Derived Column 1_12" and "Rein" cannot convert between unicode and non-unicode string data types.

Error at Camara Recibida [DTS.Pipeline]: "component "OLE DB Destination" (9902)" failed validation and returned validation status "VS_ISBROKEN".

Error at Camara Recibida [DTS.Pipeline]: One or more component failed validation.

Error at Camara Recibida: There were errors during task validation.

|||

So you are inputting a DT_STR but outputting a DT_WSTR? Is that correct? That seems like a strange thing to do to me but of course, it is entirely up to you. If it were me I would use the same type on the output as on the input - but it is your choice.

If you want to output a DT_STR then the expression should be:

[Column 12] == "0" ? [Column 12] : NULL(DT_STR,1,1252)

If you want to output a DT_WSTR then the expression should be:

[Column 12] == "0" ? (DT_WSTR, 1)[Column 12] : NULL(DT_WSTR,1)

-Jamie

P.S. Please try and include all relevant information when posting a problem. If you are trying to do some data conversion then one of the most obvious things to state is the types of the input columns and what types you require the output to have.

|||

That's fine now.

Thanks a lot indeed for your help.