Getting Null string into Sql Server database?

Accepted Solution Solved
Reply
Super Contributor
Posts: 418
Accepted Solution

Getting Null string into Sql Server database?

Hello everyone. I have another "fun" sql server question that I would love help on!

Does anyone know how to get a blank string into a sql server database table?  I have a job to write data to a sql server table, however the designer of the application cannot accept actual NULL values (NULL in sql server sense... so NOTHING being present). Instead they want an empty string to be populated into the table.   To ease understanding, please see a sql server table definition below, along with some SQL SERVER examples of how to insert a null vs inserting an empty string.

CREATE TABLE [dbo].[TESTTABLE](

[testvar] varchar(200) null

) ON [PRIMARY]

/*how to insert an actual NULL in SQL SERVER*/

insert into testtable (testvar)

values (NULL)

/*how to insert an empty string in SQL SERVER*/

insert into testtable (testvar)

values ('')

How would one do this in SAS?

In sas if you have an empty character variable, defined by something like var1='';    Then when you use a proc sql step to insert it into a sql server table, it defaults it to the NULL value, and not the empty string value... Please see the code below for an example of this.

Inserts actual NULL

data nullconfusion;

teststring='';

run;

libname inter odbc dsn='odbc_myfakeodbc' schema=dbo;

proc sql;

insert into inter.SQLDATABASETABLE(testvar)

select teststring

from nullconfusion;

quit;

run;

ALSO inserts actual null

data nullconfusion;

teststring='';

run;

libname inter odbc dsn='odbc_myfakeodbc' schema=dbo;

proc sql;

insert into inter.SQLDATABASETABLE(testvar)

select ''

from nullconfusion;

quit;

run;

If anyone can figure out how to make an actual empty string instead of a null value that would be great!  Basically all I want is the following query to pick up the records.

select * from SQLDATABASETABLE

where testvar=''

However this doesn't work since the values are NULL, and this sql statement does not pick up NULLS.

Any help is appreciated, and thanks for your time!


Accepted Solutions
Solution
‎11-20-2014 04:45 PM
Valued Guide
Posts: 3,208

Re: Getting Null string into Sql Server database?


All Replies
Solution
‎11-20-2014 04:45 PM
Valued Guide
Posts: 3,208

Re: Getting Null string into Sql Server database?

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 1352 views
  • 0 likes
  • 2 in conversation