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:
DECLARE @ORDER CHAR(10)
SET @ORDER=
(
SELECT NULL
)
IF @ORDER <> ''
PRINT 'Not an empty string'
IF @ORDER IS NULL
PRINT 'It is NULL'
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
ADVERTISEMENT
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;
}
else
{
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.
Thanks,
-Tony
View 2 Replies
View Related
Jan 21, 2015
I was running an operation to shrink file/emptyfile a data file, and then remove it.
It blocked and caused a huge mess, I suspect on the removal part. But I want to confirm that the emptyfile completed (and that the engine isn't going to try to put more data in there for when I schedule the removal part again a week or more from now).
How does the engine know not to put any more data in there, and how long does that situation last?
View 3 Replies
View Related
Jun 1, 2007
Hello,
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.
cdun2
View 14 Replies
View Related
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
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
Oct 25, 2007
Hey
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
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
Sep 26, 2006
Hello,
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
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
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
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.
Ben
View 3 Replies
View Related
Sep 15, 2015
The below code returns a NULL for all the Differential backups. I am trying to return the word Differential when there is a NULL because I can tell from when the NULLs occur that those are my differential backups. How can I get the result I want?
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
CASE
WHEN msdb..backupset.type = 'D' THEN 'Database'
[Code] .....
View 9 Replies
View Related
Jun 16, 2015
Is there a way to log connection string details for the different executables in my package in ssis 2014?
For instance, I am loading data from a flat file to SQL Server table in the connection manager I have Flat File & OLE DB connections.
I would like to log the connection string information for Flat File & OLE DB connections; along with the OnError, OnTaskFailed deatails.
View 2 Replies
View Related
Oct 29, 2013
I have a excel sheet with some data and blank columns. I have a ssis package using to import data from excel to sql table. For blank excel columns it is importing as null instead i want to show them as '0'. If data comes in it should update the data.
View 8 Replies
View Related
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:
SET CONCAT_NULL_YIELDS_NULL OFF
SELECT t1.field1+'-'+'t2.field2 AS viewField
FROM table1 t1 INNER JOIN
table2 t2 ON t1.fk1=t2.pk2
The tables look like this:
Table1
PHP Code:
create table user.dbo.table1
(
pk1 int primary key,
fk1 int,
field1 varchar(32),
FOREIGN KEY fk1 REFERENCES table2 (pk2)
)
Table2
PHP Code:
create table user.dbo.table2
(
pk2 int primary key,
field2 varchar(32)
)
table1
PHP Code:
pk1 fk1 field1
1 1 'test1'
2 2 'test2'
3 1 'test3'
table2
PHP Code:
pk2 field2
1 NULL
2 'tab2 test'
I expect to see:
PHP Code:
ViewField
test1-
test2-tab2 test
test3-
View 2 Replies
View Related
Jul 22, 2007
Hello,
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
Jul 11, 2001
example
create view v_GuestOrder
as
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
3
4 tv
I need something similar to
Select
id_Guest,
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
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?
Thanks!
View 2 Replies
View Related
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
May 21, 2015
I'm having issues with my sql server 2014 express configuration manager. I need to change protocols under Sql Server Network Configuration but there is nothing, no protocol listed, its empty.
View 3 Replies
View Related
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
Jan 12, 2008
Hi:
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.
Jt
View 3 Replies
View Related
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
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.
THANKS JOHN
View 3 Replies
View Related
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
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
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
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
Sep 9, 2014
declare @user varchar(30) = ''
if(@user is not null or @user <> '')
BEGIN
print 'I am not empty'
END
ELSE
BEGIN
print 'I am empty'
ENd
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
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),
[FinanceYear],
[New Value] = SUM([Previous_Year]+[Current_Year])
from Finance
GROUP BY [Original], [FinanceYear]
View 1 Replies
View Related
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
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
maxs
View 4 Replies
View Related