How delete "NULL" from Sql

Question

I have NULL values in my table.

SELECT [Id] ,[Mark] ,[Series] ,[Models] ,[Power] FROM [ExcelAnalysis].[dbo].[Dict]

Here the screen http://imgur.com/a/kAOYA

so i try do so

update tablename set fieldname = REPLACE ( fieldname , string_pattern , string_replacement ) update [dbo].[Dict] set [Mark] = REPLACE ( [Mark] , 'NULL' , '' )

but after executing the query, NULL values i see again

How correct delete it?

You don't seem to know what null is. And why would you want to replace it with an empty string? — juergen d
– juergen d, Commented Jun 15, 2017 at 11:41
NULL is not a value. It represents the absence of a value. — Silvermind
– Silvermind, Commented Jun 15, 2017 at 11:45

Dmitrii Bychenko · Accepted Answer · 2017-06-15 11:43:32Z

1

Compare with NULL (null value, please, notice absence of the apostrophs), not 'NULL' (string NULL)

 update [dbo].[Dict] set [Mark] = '' where [Mark] is null

answered Jun 15, 2017 at 11:43

Dmitrii Bychenko

188k20 gold badges178 silver badges231 bronze badges

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

5 Comments

psysky Over a year ago

Thanks you) i understood ,the null is not string)

psysky Over a year ago

so , Dmitry, how do this replace for all field at once select * :)

Dmitrii Bychenko Over a year ago

@D.Joe: Sorry, I don't follow you? If you want "if field is null substitute it with '' " construction, try Coalesce(field, ''). Coalesce returns 1st not null value

psysky Over a year ago

i mean replace NULL in all table at once))

Dmitrii Bychenko Over a year ago

@D.Joe: the query in the answer replaces all null values in the [Mark] fields for the entire [dbo].[Dict] table

Gordon Linoff · Accepted Answer · 2017-06-15 11:41:53Z

1

For NULL use COALESCE() or a WHERE clause:

update [dbo].[Dict] set [Mark] = '' where [Mark] is null;

NULL is not a string value.

answered Jun 15, 2017 at 11:41

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

juergen d Over a year ago

Probably would be better to not just replace NULL with an emtpy string but explain what NULL is.

Collectives™ on Stack Overflow

How delete "NULL" from Sql

2 Answers 2

5 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Related