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?
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
If I use "results to text" option when executing a query in Ms SQL Server Management Studio null value is inserted as 'NULL' string in the result. Is it possible to have blank ('') string instead of 'NULL' in the result in 2005 version.
It must be possible, but I could not find it in tools/options
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.
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
************* 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. **************************************************
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?
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)
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.
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?
In sql server 2000 - our QA pointed out that his testing for empty strings returned 200 + rows but that when he clicked in the field there were obviously a space there. This issue came up because of the script I created to replace and earlier one that queried on empty strings instead of datalength and the earlier script always reported that it had updated x number of rows regardless of how many times it was run on the same database.
QA query based on the earlier script: Select * from StringTable WHERE (LongString = '' OR LongString IS NULL)
My script: The fields are nvarchars in the newer database but older version of the database had varchars. I had created a script to replace empty strings as follows:
-- if LongString column is varchar - run varchar update else nvarchar update If exists (Select * from sysobjects o inner join syscolumns c on c.id = o.id where c.name = 'LongString' and o.name = 'StringTable' and c.xtype = 167) begin
-- update varchar LongString UPDATE StringTable SET LongString = char(32) -- Select * from StringTable WHERE ((DATALENGTH(LongString ) < 1) OR LongString IS NULL)
END Else Begin
-- update nvarchar LongString UPDATE StringTable SET LongString = char(32) -- Select * from StringTable WHERE ((DATALENGTH(LongString ) < 2) OR LongString IS NULL)
If exists (Select * from sysobjects o inner join syscolumns c on c.id = o.id where c.name = 'ShortString' and o.name = 'StringTable' and c.xtype = 167) begin
UPDATE StringTable SET ShortString= char(32) -- Select * from StringTable WHERE ((DATALENGTH(ShortString) < 1) OR ShortString IS NULL)
END Else Begin
-- update nvarchar ShortString UPDATE StringTable SET ShortString= char(32) -- Select * from StringTable WHERE ((DATALENGTH(ShortString) < 2) OR ShortString IS NULL)
My method for checking for datalength appears to work correctly why doesn't the QA script? I thought it might have to do with the nvarchar used in the table but I changed the column to a varchar and still has the same issue.
Inside my sp on the Primary server are 3 commands:
exec msdb..sp_start_job @job_name = N'JobA' -- Running on Primary ServerEXEC Server2.msdb.dbo.sp_start_job @job_name = N'JobB';Running on Secondary ServerSelect * from Server2.Table -- Running on Secondary Server
So far the commands are working just fine when I kick them off one at a time. But when I kick off the sp as a whole I notice that the timing just isn't keeping up. For instance, Step 3 is running before Step 2 is finished. It appears that as the Primary Server kicks off Step 2, it doesn't seem to care about the outcome and moves right to Step 3 which in turn provides me erroneous data.How can I get Step 3 to wait until Step 2 is actually finished keeping in mind the Secondary server is part of this equation?
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
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
HI, 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.
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.
Hi, I wanted to create an exact database in another server, so I generated script of all tables,views, store procedures and ran the script on the new server. I was able to have all objects in the new server. but when I run the following sql from sql query analyser, I get nothing in return. What do I need in order to get a valid response.
/*Truncate from all tables*/ select 'Truncate table ' + name from sysobjects where type ='u' order by name
/*Count all table rows from all tables*/ select 'select count(*) as ' +''+ name + ' from ' + name from sysobjects where type ='u' order by name
/*View all sp*/
select * from sysobjects where type ='p' and name not like 'dt%' order by name
/*View all triggers*/
select * from sysobjects where type ='tr' order by name
/*View all Views*/ use master select * from sysobjects where type ='v' order by name
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.
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.
We are writing a C application that is using ODBC to insert recordsinto a database. We have a NOT NULL column that can legitimately havean empty value, i.e. we know the value and it is empty (i.e. a zerolength string).We are using SQLBindParameter() to bind a variable to theparameterized insert statement <<in the form: INSERT INTO table VALUES(?, ?, ?)>>. We are using SQLExecDirect() to process the SQL.We are running into the problem where ODBC is converts the empty (zerolength) string into a NULL value and this errors due to the fact thatthe column is defined as NOT NULL.We do not want to redefine the column as NULL, becasue myunderstanding of the correct usage of a NULL column is to indicatethat a value is unknown or meaningless... in our case we know thevalue (it is empty) and an empty value has meaning within ourapplication.I'm sure that this issue has been seen and address thousands(millions?) of times... any guidance would be appreciated.