Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# How to convert '' (space kind of value to 0.

Posted on 2013-06-12
Medium Priority
481 Views
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
0
Question by:searchsanjaysharma
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 4

LVL 93

Expert Comment

ID: 39242051
SELECT ims, ems,
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
0

LVL 93

Expert Comment

ID: 39242055
BTW, you should not be using [n][var]char data types for this :)
0

Author Comment

ID: 39242110
How to use update on this.

As i ma having the statements as
update mstmassstudents set total=convert(varchar,convert(bigint,ims)+convert(bigint,ems)))
How to put it here in this context
0

LVL 93

Accepted Solution

Patrick Matthews earned 2000 total points
ID: 39242140
>>How to use update on this.

Seriously, storing derived data like that is usually a bad idea.  But if you insist:

``````UPDATE mstmassstudents
SET total = CASE WHEN ISNUMERIC(ims) = 1 THEN CONVERT(int, ims) ELSE 0 END +
CASE WHEN ISNUMERIC(ems) = 1 THEN CONVERT(int, ems) ELSE 0 END
``````

If total is also varchar (again, bad idea)...

``````UPDATE mstmassstudents
SET total = CONVERT(varchar,
CASE WHEN ISNUMERIC(ims) = 1 THEN CONVERT(int, ims) ELSE 0 END +
CASE WHEN ISNUMERIC(ems) = 1 THEN CONVERT(int, ems) ELSE 0 END)
``````
0

Author Comment

ID: 39242152
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
0

LVL 93

Expert Comment

ID: 39242185
I had to keep that as the total has exceptional data. like E, I, F etc.
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.

0

Author Comment

ID: 39242222
I have resolved that, thanx.
0

Author Closing Comment

ID: 39242224
tx
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backupâ€¦
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a paâ€¦
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
###### Suggested Courses
Course of the Month7 days, 9 hours left to enroll