iif(isempty(fieldName), 0, fieldName) not working

Question

Post reply

iif(isempty(fieldName), 0, fieldName) not working

Carrie-253309

SSC-Addicted

Points: 450
More actions
August 17, 2005 at 10:08 am

#90557

Any one have an idea on how to test for an empty varchar field. The field is NOT null but a white space empty (" "). I have tried to use iif(isEmpty(fieldName), 0, fieldName) but analyzer tells me that isEmpty is not a valid function.

I have to figureout how to do this since the people who built the table made everything varchar and allowing nulls. There is also no unique key on the table but I am expected sum the shipped amount field.
and no the process that fills the table can no be changed to input zero's instead of blanks!

Thanks for any Help!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

If you are using T-SQL then your query is not valid.

iif(isEmpty(fieldName), 0, fieldName)

IsEmpty is not a valid function, and IIF is not valid for an if statement

What are you trying to accomplish?

Post a table statement, and some sample data, and your expected results.

Your trying to find out if a specific field has Nulls, Blank Strings, or strings with spaces in the entire table? or if field on a specific row has a Nulls, Blank String or String with spaces?

EDIT:

-- This query will show all rows where myfield has either only spaces, or blankStrings.

Select *

from Mytable

where replace(myfield, ' ', '') = ''

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 2

(MyField IS NOT NULL) AND (RTRIM(MyField)<>'')

Kindest Regards,

Vasc

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 3

IIF is VB function

For T-SQL u can use

CASE WHEN fieldName IS NULL THEN 0 ELSE fieldName END

or

ISNULL(fieldName, 0)

or

COALESCE(fieldName, 0)

For your case it can be

CASE WHEN COALESCE(FieldName, '') = '' THEN 0 ELSE FieldName END

To check an empty varchar filed Vasc's answer is correct

Regards,
gova

Vijay-251247 Say Hey Kid Points: 684 More actions · Answer 4

have you tried

if isnull(fieldname, '') = ''

select fieldname = 'xyz'

am not too sure... the above statement replaces a space with 'xyz' string.

regards

sushila SSC-Dedicated Points: 35293 More actions · Answer 5

what about:

datalength(fieldName) is not null

datalength(rtrim(fieldName)) = 0

**ASCII stupid question, get a stupid ANSI !!!**

Carrie-253309 SSC-Addicted Points: 450 More actions · Answer 6

Ok a simple example of the table i'm faced with is:

orderNum shipamt invoicenum

1 4.56 6785943

1 5634251

2 7.89 8978764

3 1233454

I need to select sum shipamt grouped on orderNum:

SELECT orderNum, Sum(Convert(decimal(10,2), shipamt)) as totalShipped FROM invoiceData GROUP BY orderNum

Thus Outputting the results of orderNum and total shipped amount

but the sum will fail for orderNum 1 above because the shipamt field holds an empty white space not a zero or a null

I will be trying the lines given.

noeld SSC Guru Points: 96590 More actions · Answer 7

SELECT orderNum, Sum(Convert(decimal(10,2), shipamt)) as totalShipped FROM invoiceData

WHERE shipamt <> ''

GROUP BY orderNum

* Noel

Carrie-253309 SSC-Addicted Points: 450 More actions · Answer 8

Thanks noeld!

I actually thought of that. And am doing it now. I missed the simple solution by letting my manager make suggestions on how to work around the problem.

Stupid me.

After I got him out of my office I looked at it and said "i am such an idiot".

Thanks for all the help, everybody!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

That's great feeling isn't it . Now you know you don't ahve to listen to him ever.

David le Quesne SSCertifiable Points: 5229 More actions · Answer 10

You can use the T-SQL ISNUMERIC() function, it will return 0 for a space and 1 for a number.

David

If it ain't broke, don't fix it...