Null, Empty Strings, And Efficiency

Oct 12, 2004

************* Edited by moderator Adec ***************

Inserted missing < code></ code> tags. Always include such

tags when including code in your postings. Don't force the

moderators to do this for you. Many readers disregard

postings without the code tags.


Well met,

Let's say I have a web form that allows users to insert and update records in a SQL database. Is it better to set empty web controls (textbox, etc.) to DBNull or let it go as an empty string into the database?

Example code:

if (tboxNAME.Text == "")


sqlCommand1.Parameters["@NAME"].Value = DBNull.Value;




sqlCommand1.Parameters["@NAME"].Value = tboxNAME.Text;


Is the above overkill? It seems like it would be a good idea to set fields which the user empties back to Null rather than an empty string.



View 2 Replies


SQL Server 2014 :: NULL And Empty Strings

Oct 29, 2015

I have always (or at least intended to) treat NULL and empty strings separately in my SQL querying history. Now I have run across something that mystifies me (but probably shouldn't) that I would like an explanation for.

Consider this bit o' code:

IF @ORDER <> ''
PRINT 'Not an empty string'


Run this and you will get:

It is NULL

I was expecting:

Not an empty string
It is NULL

Why is NULL not passing the 'not an empty string' test? In other words, how does NULL = '' ? Is NULL cast to an empty string for this comparison?

View 9 Replies View Related

Efficiency In Inserting Null Values Into Fields Which Allow Nulls.

Aug 9, 2004

I have fields in my table which allow nulls. Is it efficient to not insert anything (the field automatically shows up as null in this case) and leave or store some value into it. The field is a smallint field?


View 2 Replies View Related

Handling Empty Strings In DTS

Jun 1, 2007

I have a transformation in which the column of data at the flat file source is nine characters long, and typically contains a string of six or seven zeros with a non-zero number in the last two or three characters. If none of the records in that column were an empty string, I think I could get away with this:

DTSDestination("TTLCrd") = CInt(DTSSource("Col004"))

The destination is a SQL Server 2000 table, and the column is of type Integer. What do I do when Col004 is an empty string? I've tried a couple of different IF statements, but they have not worked. Empty strings need to become zero values.

Thank you for your help.


View 14 Replies View Related

Remove Empty Strings From Results

Feb 6, 2007

i am makin an address block

trying to make it (address1 + ' ' + address2 + ' ' + address3) as address

is there a way to get rid of the address2 if there is nothing in it

View 3 Replies View Related

Query On Empty Strings In A Table

Oct 25, 2007


Is it possible to search for a column without a value?

$query="select id from table1 where col2=''"; (this didnt work, but how do I do it??)

I need the id for the row that has the col2 empty.


View 13 Replies View Related

Change Not Null To Null, Default Value To Empty

Aug 3, 2005

hi,my structure table in database:Amount float(53) not null default 0when i try to run his script:alter table ABC alter column Amount float(53) nullit can only set the Amount to allow null, but can't set the defaultvalue to empty.anyone know how to set the field to allow null and default set toempty, no value.thanks

View 5 Replies View Related

Values In LEFT JOIN Need To Be Empty Strings (was SQL Query Question)

Sep 26, 2006


I have a SQL database where I am attempting to perform a complicated query that I cannot seem to figure out. I am using SQL Server.

I have 4 tables (TableA, TableB, TableC, and TableD). TableA and TableB are guaranteed to have a relationship.

TableC and TableD are guaranteed to have a relationship.

The trick is, I need to link between TableA and TableC essentially using a LEFT JOIN. I need to retrieve all of the values from TableA regardless and the information from TableC and TableD if there is a link, if there isn't a link, then the values from TableC and TableD need to be empty strings.

Does anyone know how I can do this? I've been trying for the last 5 hours without any luck. I feel I'm close, but there is something I feel I'm overlooking.

Thank you SO much for your help!

View 5 Replies View Related

Storage And Performance Of NULLs And Empty Strings (was Noobish Question)

Feb 11, 2005

Am I right in assuming that when I have a column where all fields contain NULL, this does not increase the total data storage size if my database? Also, what kind of impact would it have on performance?

And what if I inserted "" in varchar columns? I would think the increase in size would be marginal?

The reason I'm asking is that I want to use an existing table and stored procedures for another purpose, but only need half of the columns. But it would significantly simplify application development.

View 3 Replies View Related

Scd Type 2 Problem With The Data Having Empty Strings In Business Keys

Aug 24, 2007

I am having data where there are empty string in the business keys which should be used for Slowly changing dimesnion type 2, how do i over come this as due to empty strings i am getting new rows even though the rows havent really changed.

example of data is name and salary are business keys

name salary age address
dev 23 klddldldlk
sdfg 24 34 kdlddlkd

when the same is given as input the row
dev 23 klddldldlk
is coming as anew row where it already exists how do i over come this

View 4 Replies View Related

Fuzzy Grouping Matching Nulls To Empty Strings/spaces

May 30, 2007

Will the fuzzy grouping task match a null value to an empty string (or spaces)? I've got 5 columns I'm matching on, and one of them may be null for certain rows but an empty string for others. Given the 4 other columns may match, will this difference stop similar columns being grouped together?

(Someone's modified my grouped data since it was deduped, which takes a while, and I'm hoping for a quick answer on this).

Thanks in advance.


View 3 Replies View Related

Concat Null Strings In View

Jan 17, 2005

I want to concat strings such that if one string is null, it is treated as an empty string. For example 'abc' + NULL = 'abc'.

SQL Server has the CONCAT_NULL_YIELDS_NULL defaulted to ON, and it needs to be on in most cases when dealing with indices. (INSERT, DELETE, UPDATE, etc.) However, when I am running a select, and one field has a null value, I still want to see the other fields in the resulting string.

I can do this in a function, but I need a view, and the view doesn't seem to let me save a command with 'SET' in it. (However, it does let me run the command! And it works!) It's just when I try to save it, I get a syntax error, so the view is unavailable to me as I want it.

The command that works, but won't save is:

PHP Code:

SELECT t1.field1+'-'+'t2.field2 AS viewField
  FROM table1 t1 INNER JOIN
           table2 t2 ON t1.fk1=t2.pk2 

The tables look like this:


PHP Code:

 create table user.dbo.table1
  pk1 int primary key,
  fk1 int,
  field1 varchar(32),
  FOREIGN KEY fk1 REFERENCES table2 (pk2)


PHP Code:

 create table user.dbo.table2
  pk2 int primary key,
  field2 varchar(32)


PHP Code:

 pk1 fk1 field1
 1     1    'test1'
 2     2    'test2'
 3     1    'test3' 


PHP Code:

 pk2  field2
 1     NULL
 2     'tab2 test' 

I expect to see:

PHP Code:

test2-tab2 test

View 2 Replies View Related

Empty &&<&&> IS NOT NULL

Jul 22, 2007


I have a query that returns the appropriate values I need, however there is one field I'd like to add and utilize but my problem is I only want to use it if it contains data.

If I filter it with IS NOT NULL it returns all the records, including the empty records. The field is simply empty, and doesn't come back as NULL. If I filter it with =' ' , it shows all the records with the empty records only.

I need to do the opposite, be able to filter it only if it's not empty.

Any help would be appreciated.

View 4 Replies View Related

Empty String To Null

Jul 11, 2001

create view v_GuestOrder
Select T1.id_Guest,T2.OrderName
from Guest T1
left join Order T2 T2.id_order = T1.Id_order

select * from v_GuestOrder
Id_Guest OrderName
-------- -----
1 spoon
2 phone
4 tv

I need something similar to

case orderName
when '' then Null -- Sql server gives error in thsi case
end as orderName
from v_GuestOrder

So I need to assign NULL to OrderName is query return empty string,
it will be treated by Crystal reports as Null

Please help , thanks

View 3 Replies View Related

NULL On Empty Fields

Mar 16, 2008

How can I make empty cells show NULL on my table? Some cells show NULL others won't. Does this mean that they have contents?

The reason being is that, when I use the code

Select *
From Employees
Where JobDescription1 Like '%montly%'

Those with empty jobdescription1 show with the legitimate results.

Any help please?


View 2 Replies View Related

COALESCE: Empty Instead Of NULL?

Mar 25, 2008

I've inherited a terribly designed database. When cells in the tables have nothing in them, rather than being NULL, they're just empty. So now I can't use COALESCE...

Is there a way for COALESCE to check if a cell is empty instead of NULL? And if not, is there a way to get around this?

View 1 Replies View Related

Checking If String Is NULL Or EMPTY In SQL

Nov 9, 2007

I need to check in my Stored procedure if the information passed is null or empty so I can decided to insert the new value or keep the old. How do I accomplish this please in T-SQL. Thanks in advance.

View 6 Replies View Related

Insert Empty String Or Null To Sql Db

Jan 12, 2008

Trying to insert null value into sql table, but not working, if I use:
 if (strMyText.Length == 0)        command.Parameters.Add("@Text", DBNull.Value); // or using:("@Text", null), or using:("@Text", DBNull) else         command.Parameters.AddWithValue("@Text", strMyText); 
When I go back to table, I see the value is: 'NULL', has single quotation mark, suppose to be: NULL
Where is the problem?
Thanks a lot.

View 3 Replies View Related

Null Or Empty String Use More Space?

Aug 9, 2005

Hi all,   I have some columns in my database which allows null.  I want to know if leaving the field to be NULL or storing an empty string into the field, which will take up more space?? if the field type is varchar(100)

View 5 Replies View Related

HELP : Empty String To Null Conversion By SQL

Nov 10, 2005

Hi folks,

I've have about 100 tables, for some reasons, column values that are originally NULL was inserted as emtpy string. So, I am wondering if I can write JUST ONE SQL (hopefully don't have to specify the field names in the SQL as well) for each table so that all the empty strings will be converted back to NULL.


View 3 Replies View Related

T-SQL (SS2K8) :: Field Has No Value But Is Not NULL Or Empty?

Aug 13, 2015

I added a new field to an existing ETL process which uses SSIS to ingest a CSV file. The new field in the file, Call_Transaction_ID, will not always be populated for every record and so can be NULL or empty for certain records.

Here's the problem:After the file is extracted into a staging table, the Call_Transaction_ID field is showing blank or empty when it has no ID for that particular record. The problem is when I try to then ETL this data into a fact table - I'm trying to set the Call_Transaction_ID field to -1 if it is NULL or empty, however SQL Server doesn't see the field as empty even though there is no value in the field so -1 will NEVER return.

Using a WHERE DATALENGTH(Call_Transaction_ID) = 0 returns 0 records, again because SQL Server doesn't see the field as empty or NULL.

What do I do now to get around this? How do I fix it?

View 5 Replies View Related

Analysis :: Convert A Zero To Null Or Empty?

Sep 10, 2015

I am working on a cube and I want to hide the results of a calculation if it is zero.

It is an inventory problem based on a transaction pattern. (add plus one to the inventory, add minus one to the inventory).

The sum of those two is zero, but the cube get messed but by thousands of zero.

I found some solutions of displaying 0 for NULL but I want to dispaly NULL (or empty) for 0.

View 2 Replies View Related

NULL Values Vs Empty String Vs Space

Oct 25, 2006

How do I define a field to have the default value = ''. Not NULL but not a space either in SQL Server 2005?

View 10 Replies View Related

Within A SELECT, How Do I Replace An Empty Or Null Value With A Value From Another Table?

Apr 20, 2008

Hello,I'm a beginner in SQL and I have been searching through the SQL Cookbook and Google but I can't seem to find an example of what I want to do. I want to create a report that will return names and emails using two of my tables. I want to use the email in my primary table in the select but if it is null or empty I want to replace it with an email from my secondary table. Below is what I would like to do but I got a syntax error with it in SQL Server 2000. SELECT MemberID As ID, MemberFirstName As FirstName, MemberLastName As LastName, (IF MemberEmail = '' THEN SELECT TOP 1 OtherEmail FROM OtherTable WHERE OtherID = MemberID) As EmailFROM PrimaryTableThanks for your time.Jason  

View 8 Replies View Related

T-SQL (SS2K8) :: Check If Variable Is Empty Or Null?

Sep 9, 2014

declare @user varchar(30) = ''
if(@user is not null or @user <> '')
print 'I am not empty'
print 'I am empty'

The output should be 'i am empty' but when i execute this i am getting ' i am not empty'. even i did browse through but i don't find the difference in my logic.

View 9 Replies View Related

Check For Null Or Empty Values And Return 0

Mar 13, 2014

I am using the below query to calculate column values. But I need to return zero when a column values is empty or null.

select [Funding] [Fundings],
[Original] AS [Originals],
[Variance] = SUM([Previous_Year]-[Current_Year]),
[SumValue] = SUM([CurrentYear]/4),
[ActualValue] = SUM([Variance] * 0.75),
[New Value] = SUM([Previous_Year]+[Current_Year])
from Finance
GROUP BY [Original], [FinanceYear]

View 1 Replies View Related

Parameter Value From Empty Text Box - Null Or 0 Length?

Jul 20, 2005

Hi,I run a stored procedure with a parameter given from a text box in an accessadp. If the text box is empty then what is passed to the parameter? I can'tseem to get it to flag up as either null, or 0 length.Any tips?Cheers,Chris

View 1 Replies View Related

How To Pull Null (empty) Records From SQL Database

Dec 5, 2007

How to pull null (empty) records from SQL database,

what query am I suppose to use to get result

help me

thank you

View 4 Replies View Related

Transact SQL :: How To Get Zero Or Null Value For Empty Results In Server

Oct 7, 2015

I have written one query like this 

select staffid,staffname,deptname

From staff s join dept d on s.deptid=d.deptid

This query we have no results 

I need this results

staffid staffname deptname
null     null          null

View 7 Replies View Related

Web Service Dataset With Null Or Empty Spaces.

Sep 6, 2007

I am using XmlDataDocuments returned by webservices and query in the reports to populate the reports.

The issue I have is if the dataset used to populate the XML document has any nulls or empty spaces in any field the whole column or row is missing in the data generated by my query in the Reports. Sometimes the whole data is not being returned to the reports.

Sample query I use

<Method Namespace="" Name="SelectReportRegisters">
<Parameter Name="p_registerType">
<Parameter Name="p_registerName">
<Parameter Name="p_asOfDate">

I have tested the webservice with sample data and it works well, returns data.

I use the ISNULL() in my stored procedures to avoid nulls but with empty strings, replacing them with a default value in the managed code is expensive and slows down the reports.
Is there some feature I am missing because of which nulls and empty strings are causing this problem.

I am using Reporting services 2005.

Any pointers will be truly appreciated.

View 2 Replies View Related

Detect And Convert An Empty String (from Textbox) To Null?

Jun 9, 2007

 Hi all,I have this code that I use for my Search function:SELECT DISTINCT [MUSIC_TITLE], [MUSIC_ORIGINAL_SINGER], [MUSIC_PERFORMER]FROM t_musicWHERE (@MUSIC_TITLE IS NULL OR [MUSIC_TITLE] LIKE '%' + @MUSIC_TITLE + '%') AND (@MUSIC_ARTIST IS NULL OR ([MUSIC_ORIGINAL_SINGER] LIKE '%' + @MUSIC_ARTIST + '%' OR [MUSIC_PERFORMER] LIKE '%' + @MUSIC_ARTIST + '%')) But right now if I don't enter anything in one of the textbox (2 have two, either of them can be left empty), the above Sql statement doesn't return anything since ADO.NET can't tell an empty textbox and treat it at null... So anyone please help me how to detect an empty textbox and set that to null for the above SQL statement to work. (It work in SQL Manager Studio, when I set one of the parameter = null.) I'm very new to ASP.NET stuffs, so if someone can help me to convert that function to code-behind and help me to call it from the .aspx, that would be even better as I don't want to put the code in my .aspx page... But I'm not quite there yet. Thank you all,Kenny.  

View 19 Replies View Related

How To Pass Null Value To The Database If A Form Field Is Empty?

May 31, 2004

How can I pass into the database (@User_fax = null) if the fax form field is empty, from a command type Stored Procedure? For example:

Dim CmdUpdate As New SqlCommand("Form2_NewUser", strConnection)
CmdUpdate.CommandType = CommandType.StoredProcedure

CmdUpdate.Parameters.Add("@User_fax", SqlDbType.char, 9)
CmdUpdate.Parameters("@User_fax").Value = fax.Text()


And, the stored procedure inside Sql server:

USE market1
ALTER PROC Form2_NewUser
@User_id bigint, @User_fax char(9),...


SET User_fax = @User_fax, ...

WHERE User_id = @User_id

Thank you,

View 4 Replies View Related

Converting Empty String To Null When Inserting/updating

Mar 10, 2006

    I am using the following query to calculate date differences:select ..........DATEDIFF(d,  recruitment_advertising.advertising_date, career_details.RTS_Email AS Datetime) AS Ad_to_RTS_days FROM .....I have stored all my dates as NVARCHAR because of the issues with localization.If the value is an empty String my output is eg: -38700. which is way off and incorrect. Some of the values in my table are NULL and they produce the correct result.Is there a T-SQL statement to replace empy Strings with the NULL value in my tables.I'd like to use it as a trigger when inserting or updating to convert empty strings to NULLbefore the values are inserted.Thanks guys.

View 1 Replies View Related

Copyrights 2005-15, All rights reserved