T-SQL assign NULL to Text type field

Question

I try to execute next code to replace all empty strings to NULL, where column type is TEXT. But this code seems to do nothing.

UPDATE mytable SET textField = NULL WHERE DATALENGTH(textField)=0

What is right way here to null this column?

UPD. OK, this is really work. It was just weird data showing of Embarcadero DBArtasian

That should work. Presumably you don't have any truly empty strings. — Martin Smith
– Martin Smith, Commented Mar 16, 2012 at 14:05
Is your table name table? You should put square brackets around it like so: [table] — Jeff
– Jeff, Commented Mar 16, 2012 at 14:06
@Martin Smith - I checked with SELECT * FROM mytable WHERE DATALENGTH(textField)=0 and there are a lot of empty text fields — nahab
– nahab, Commented Mar 16, 2012 at 14:08
Well there's no reason why those wouldn't qualify for the UPDATE too then. Any triggers on the table? — Martin Smith
– Martin Smith, Commented Mar 16, 2012 at 14:10

Jirka Hanika · Accepted Answer · 2012-03-16 14:07:26Z

2

Your way is correct. Play a little with CAST and RTRIM, maybe you have some spaces in the field.

answered Mar 16, 2012 at 14:07

Jirka Hanika

13.7k3 gold badges52 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sathiya · Accepted Answer · 2012-11-20 13:50:17Z

2

Please convert the Text field to varchar, since LEN cannot be used on Text fields. This worked for me

UPDATE mytable SET textField = NULL WHERE LEN(convert(varchar(1000),textField )) = 0

answered Nov 20, 2012 at 13:50

Sathiya

211 bronze badge

Massimiliano Peluso · Accepted Answer · 2012-03-16 14:08:05Z

0

probabily the where condition does not return anything

try do do the below to see how many record will be affected

SELECT * from Table WHERE DATALENGTH(textField)=0

answered Mar 16, 2012 at 14:08

26.8k6 gold badges64 silver badges74 bronze badges

Vinnie · Accepted Answer · 2012-03-16 14:13:46Z

0

UPDATE mytable 
SET textField = NULL 
WHERE LEN(LTRIM(RTRIM(textField))) = 0

answered Mar 16, 2012 at 14:13

Vinnie

3,9471 gold badge28 silver badges30 bronze badges

You can't use any of those functions on a text column. And wouldn't need to trim anyway as LEN ignores trailing space.