September 17, 2013 at 11:06 am
Hi,
i am trying to convert a nvarchar column to a decimal value.
the nvarchar column has empty records as well as NULL values.
now when i try to convert the values with:
select CAST(round(column,2) AS decimal(18,2)) as column
from tablename
all goes well, but the empty and NULL values appear as 0.00 value but i want them to stay empty
can anyone help me on this , how to achieve this.
regards,
Bryan
September 17, 2013 at 11:41 am
bryan van ritter (9/17/2013)
Hi,i am trying to convert a nvarchar column to a decimal value.
the nvarchar column has empty records as well as NULL values.
now when i try to convert the values with:
select CAST(round(column,2) AS decimal(18,2)) as column
from tablename
all goes well, but the empty and NULL values appear as 0.00 value but i want them to stay empty
can anyone help me on this , how to achieve this.
regards,
Bryan
This isn't making sense. If you do a cast/convert on a NULL, you still get a NULL.
If the field contains an empty string, or only spaces, you will get an error.
select ROUND(CAST('' AS decimal(18,2)), 2)
select ROUND(CAST(NULL AS decimal(18,2)), 2)
Also, you should be doing the rounding outside the CAST.
Like this:
ROUND(CAST(column AS decimal(18,2)), 2) as column
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 17, 2013 at 11:48 am
There is no such thing as an empty decimal. The reason you are seeing '' being converted to 0.00 is because when you round an empty string it will implicitly convert it to 0. Your NULL values will remain as NULL.
I would HIGHLY recommend changing the datatype of the table if at all possible. Storing numeric values in a varchar is fraught with errors and performance issues.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 17, 2013 at 11:55 am
bryan van ritter (9/17/2013)
Hi,i am trying to convert a nvarchar column to a decimal value.
the nvarchar column has empty records as well as NULL values.
now when i try to convert the values with:
select CAST(round(column,2) AS decimal(18,2)) as column
from tablename
all goes well, but the empty and NULL values appear as 0.00 value but i want them to stay empty
can anyone help me on this , how to achieve this.
regards,
Bryan
The ROUND function is what's doing the conversion. The following illustrates this point. Note the readily-consumable DDL that can you can copy/paste.
create table #temp (
columnname nvarchar(10));
insert into #temp(columnname) values('123.45');
insert into #temp(columnname) values(null);
insert into #temp(columnname) values('');
insert into #temp(columnname) values('234.56');
select round(columnname, 2), CAST(round(columnname,2) AS decimal(18,2)) as cast_value
from #temp
drop table #temp;
My guess is this is why you have the ROUND function in there to begin with. If you want to convert the strings of numbers to actual numbers, the NULLs will take care of themselves, but you're going to have to decide how to handle the empty strings and then allow for it. You can code it to any decimal (18, 2) value. Here's one example that codes it to a NULL.
select case when columnname = '' then null else cast(columnname as decimal(18, 2)) end cast_value
from #temp
The real question here is why you're storing numeric values in an nvarchar column. You should always pick the data type that's appropriate to the data you want to store.
September 17, 2013 at 12:43 pm
Hi,
thanx for the quick answer. the last one did the trick. i know you should use the right data type in the first place,
but when taking over an existing environment and the customer wants a quick answer you search for
a fast and quick answer. so later on we will be changing the data type.
thanx in advance,
bryan
September 17, 2013 at 1:08 pm
You could streamline that a little and drop the case expression if you wanted to.
select cast(nullif(columnname, '') as decimal(18, 2))
from #temp
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply