Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 find empty string

Author  Topic 

doran_doran
Posting Yak Master

179 Posts

Posted - 2008-08-06 : 12:08:56
select id, invoice_id from slip where invoice_id=''

why is this statement gives me records where invoice_id=0?

There are only 5 records that has empty string.

Please suggest.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-06 : 12:10:47
Empty string is converted to zero.
That tells me that Invoice_ID column is INT, not VARCHAR.

Try
select id, invoice_id from slip where invoice_id IS NULL



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

doran_doran
Posting Yak Master

179 Posts

Posted - 2008-08-06 : 12:27:04
Hi Peso,

So in SQL Server all empty string is converted to zero. It's good and bad for me. Cause Invoice id = 0 mean we have not bill out clients. 1 mean is pending for supervisor approval, 2 means we billed it. i am using web services to update this table and sometimes it times out and the result is empty string or 0 is being populated. btw, invoice_id field is float.

Thanks for your help.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-06 : 13:43:48
select id, invoice_id from slip where invoice_id > 0




E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-06 : 22:09:14
quote:
Originally posted by doran_doran

Hi Peso,

So in SQL Server all empty string is converted to zero. It's good and bad for me. Cause Invoice id = 0 mean we have not bill out clients. 1 mean is pending for supervisor approval, 2 means we billed it. i am using web services to update this table and sometimes it times out and the result is empty string or 0 is being populated. btw, invoice_id field is float.

Thanks for your help.



Only in your specific context where you assign an empty string to an integer. SQL Server will implicitly convert the string to integer.

invoice_id is float ? Why ? you mean you may have invoice_id = 1.5 where you partially billed your customer or maybe invoice_id = 2.36983333 where customer partially paid for the invoices ?

Use INTEGER data type if you don't required the decimal points at all


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page
   

- Advertisement -