searchsanjaysharma
asked on
How to convert '' (space kind of value to 0.
I have two fields ims and ems field where in few values are empty not null ie ems=''
How to convert empty value (i.e not null) or blank value as 0
Some where ims is empty and somewhere ems is empty
example
ims ems total
10 10
20 20
10 30 40
40 40
40 50 90
The total should be ims + ems
both are of type varchar,
when i pick them i convert to int and then add
but the rows which contains empty value does nt get added up
how to make them consider as 0
How to convert empty value (i.e not null) or blank value as 0
Some where ims is empty and somewhere ems is empty
example
ims ems total
10 10
20 20
10 30 40
40 40
40 50 90
The total should be ims + ems
both are of type varchar,
when i pick them i convert to int and then add
but the rows which contains empty value does nt get added up
how to make them consider as 0
BTW, you should not be using [n][var]char data types for this :)
ASKER
How to use update on this.
As i ma having the statements as
update mstmassstudents set total=convert(varchar,conv
How to put it here in this context
As i ma having the statements as
update mstmassstudents set total=convert(varchar,conv
How to put it here in this context
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I had to keep that as the total has exceptional data. like E, I, F etc.
So i had to keep that
Here again the problem would be there as isnumeric will not work for values like 13.7
So i had to keep that
Here again the problem would be there as isnumeric will not work for values like 13.7
I had to keep that as the total has exceptional data. like E, I, F etc.
So i had to keep that
So i had to keep that
Please take a step back and restate your question, because it seems to me that you have not actually communicated all of the relevant requirements.
Here again the problem would be there as isnumeric will not work for values like 13.7
What do you mean by "will not work"? ISNUMERIC('13.7') returns 1.
If the problem is that it is truncating down to 13, well, in your original question you said you were converting these values to integers.
Again, please restate your question.
ASKER
I have resolved that, thanx.
ASKER
tx
CASE WHEN ISNUMERIC(ims) = 1 THEN CONVERT(int, ims) ELSE 0 END +
CASE WHEN ISNUMERIC(ems) = 1 THEN CONVERT(int, ems) ELSE 0 END AS [total]
FROM SomeTable