SQL - Convert NULL to string.empty

Question

I'm trying to perform some upgrade maintenance on our database. I need to move 3 columns of data from all rows of one table and insert that data as new rows in a new table.

INSERT INTO [dbo].[SnmpSettings]([NetworkDiscoveryId], [RoCommunities], [RwCommunities])
    SELECT id, Ro_Community, RW_Communities
    FROM [dbo].[Network_Discovery]

The above code would work fine, but Ro_Community and RW_Communities allow NULL where as RoCommunities and RwCommunities do not allow NULL. How should I convert NULLs to the empty string and then insert into my new table?

EDIT:

INSERT INTO [dbo].[SnmpSettings]([NetworkDiscoveryId], [RoCommunities], [RwCommunities])
    SELECT id, Ro_Community, RW_Communities
    ISNULL(Ro_Community,'')
    FROM [dbo].[Network_Discovery] 


Msg 102, Level 15, State 1, Line 27
Incorrect syntax near 'Ro_Community'.

I apologize for how naive I am in this bit of work. I've edited in my attempt to follow your instruction, but it does not compile. — Sean Anderson
– Sean Anderson, Commented Jun 1, 2012 at 23:53
SELECT id, isnull(Ro_Community, ''), isnull(RW_Communities, '') — Nikola Markovinović
– Nikola Markovinović, Commented Jun 1, 2012 at 23:54

bfavaretto · Accepted Answer · 2012-06-02 00:16:11Z

17

SELECT ISNULL(Ro_Community, '')

or

SELECT COALESCE(Ro_Community, '')

Note: COALESCE is supported since SQL Server 2005. It is part of the ANSI-92 SQL standard, so many suggest it's preferred over ISNULL. However there are also reports that it is a bit slower.

edited Jun 2, 2012 at 0:16

answered Jun 1, 2012 at 23:53

bfavaretto

72k18 gold badges118 silver badges162 bronze badges

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

2 Comments

Tot Zam Over a year ago

ISNULL was way faster for me.

bfavaretto Over a year ago

@TotZam That's what I said, COALESCE is said to be slower

Collectives™ on Stack Overflow

SQL - Convert NULL to string.empty

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related