nvarchar -> numberic/decimal problems

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

nvarchar -> numberic/decimal problems

Author

Topic

hardwood
Starting Member

19 Posts

Posted - 2010-06-08 : 15:11:03

So one of my tables is in this nvarchar format:

79.47;0;0
2.7;0;0
63.94;0;0
0;0;0
etc...

and I want it displayed numerically with 2 decimal places. I used an isnumeric() function on the table and got 0's back, and I tried taking just the left side of the string but the results are uneven.

I also tried convert(decimal(3,2),[colu1]) and convert(numeric(18,2),[colu1]) just from syntax i found on the internet, and it said it can't convert it.

Any tips?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-08 : 15:17:54

Something like this probably

declare @t table (col1 varchar(100))
insert @t
select '79.47;0;0'
union all select '2.7;0;0'
union all select '63.94;0;0'
union all select '0;0;0'
union all select '0'

select case when charindex(';',col1) > 0 
then convert(decimal(10,2),substring(col1,1,charindex(';',col1)-1))
else convert(decimal(10,2),col1)
end 
from @t

hardwood
Starting Member

19 Posts

Posted - 2010-06-08 : 15:20:32

how do I include that in this query though (d.Tax is in nvarchar format):

Select
d.Tax

From
ARHeader h
join ARDetail d on h.Voucher = d.Voucher and h.CompCode = d.CompCode
join ARItem i on d.[LineNo] = i.[LineNo] and d.Voucher = i.Voucher

Where
i.CompCode='Will' and h.AID=475568982 and d.TransNo=0

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-08 : 15:22:05

[code]Select case when charindex(';',d.Tax) > 0
then convert(decimal(10,2),substring(col1,1,charindex(';',d.Tax)-1))
else convert(decimal(10,2),d.Tax)
end as Tax
From
ARHeader h
join ARDetail d on h.Voucher = d.Voucher and h.CompCode = d.CompCode
join ARItem i on d.[LineNo] = i.[LineNo] and d.Voucher = i.Voucher
Where
i.CompCode='Will' and h.AID=475568982 and d.TransNo=0[/code]

hardwood
Starting Member

19 Posts

Posted - 2010-06-08 : 15:23:30

you're the best

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-08 : 15:29:50

I'm not...but still...thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources