CODEWITHSUNDEEP
sql-serversql-server-2008
AskMe
AskMe

Reputation: 2561

Search blank numeric value from a column SQL Server

I have a numeric column having blank in DB table. I'm trying this :

select top 10   
       CHQNO,
       ACCOUNT,
       SURNAME,
       OTHRNAME,
       CHQAMT,
       BANKNO,
       PYMTNO,
       GLACCTNO 
from   table1 
where  CHQAMT = ''

Note: CHQAMT column type is decimal

and getting error: Error converting data type varchar to numeric.

How to resolve this?

Upvotes: 0

Views: 3145

Answers (3)

ѺȐeallү
ѺȐeallү

Reputation: 3017

You could try checking like this...

select top 10   
           CHQNO,
           ACCOUNT,
           SURNAME,
           OTHRNAME,
           CHQAMT,
           BANKNO,
           PYMTNO,
           GLACCTNO 
    from   table1 
    where  ( CHQAMT is null OR CONVERT(varchar,CHQAMT) = '' OR CONVERT(int, CHQAMT) = 0 )

Upvotes: 0

etsa
etsa

Reputation: 5060

If CHQAMT is numeric, you should check if it's NULL (blank = '' only for string)

select top 10 
       CHQNO,
       ACCOUNT,
       SURNAME,
       OTHRNAME,
       CHQAMT,
       BANKNO,
       PYMTNO,
       GLACCTNO 
from   table1 
where CHQAMT IS NULL;

Some simple tests:

CREATE TABLE TEST1 (NUM INT, STRING VARCHAR(10));
INSERT INTO TEST1 VALUES (NULL, NULL);
INSERT INTO TEST1 VALUES (1, '');
INSERT INTO TEST1 VALUES (0, 'Zero');

 SELECT * FROM TEST1 WHERE NUM IS NULL;

Output:

    NUM STRING
1   NULL    NULL

SELECT * FROM TEST1 WHERE STRING IS NULL;

Output:

    NUM STRING
1   NULL    NULL

-----Next one (for datatype VARCHAR would have different result in Oracle, but this is not your case)

SELECT * FROM TEST1 WHERE STRING = '';

Output:

    NUM STRING
1   1

And at last:

SELECT * FROM TEST1 WHERE CAST(NUM AS VARCHAR(10))= '';

Output (nothing): actually I think it has not any meaning.

Upvotes: 2

Avitus
Avitus

Reputation: 15958

If you have a numeric column then the value can't be blank. The reason you're getting the error message "Error converting data type varchar to numeric." is that when you compare to '' it is trying to convert the string '' to a number to do the comparison.

If you're looking for null values you have to change your query to be:

select top 10   
       CHQNO,
       ACCOUNT,
       SURNAME,
       OTHRNAME,
       CHQAMT,
       BANKNO,
       PYMTNO,
       GLACCTNO 
from   table1 
where  CHQAMT is null

If you really are looking for a blank, which i suspect is not the case you'd need to change your query to be:

select top 10   
       CHQNO,
       ACCOUNT,
       SURNAME,
       OTHRNAME,
       CHQAMT,
       BANKNO,
       PYMTNO,
       GLACCTNO 
from   table1 
where  convert(varchar,CHQAMT) = ''

Upvotes: 0

Related Questions