ms sql server 2005 error converting varchar to numeric

Posted on 2011-09-28
Last Modified: 2012-05-12
not sure why are having issues converting here..

 declare @home_spread nvarchar(max)

declare @home_spread_numeric numeric(30,2)

-- the value will look like '+33.5' or '-4.5 or +7'
set @home_spread = (select home_team_spread from schedule_shoe where shoe_id = @shoe_id)

-- ==================================================
set @home_spread = (replace(@home_spread,'-',''))

set @home_spread = (replace(@home_spread,'+',''))

-- =================================
-- ======================================
if @home_spread not like '%.%'
     set @home_spread_numeric = cast(@away_spread as numeric(30,2)) + '.0'
Question by:GlobaLevel
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
  • Learn & ask questions
LVL 10

Author Comment

ID: 36720784
Any ideas?
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 36813856
Where it fails?
And in the last line  
set @home_spread_numeric = cast(@away_spread as numeric(30,2)) + '.0'
where is the @away_spread came from? You don't need the " +'0' " because you are converting already to 2 decimal places
LVL 22

Accepted Solution

8080_Diver earned 500 total points
ID: 36814284
I suspect thaqt the line in question shoud read:
set @home_spread_numeric = cast(@home_spread as numeric(30,2)) + '.0'

Open in new window

As previously stated, forget the '.0', not only because it is unneeded but also because you are trying to add an Alphanumeric (i.e. CHAR or VARCHAR) to a numeric.  If you feel compelled to add 0 tenths to the value, use:
set @home_spread_numeric = cast(@home_spread as numeric(30,2)) + 0.0

Open in new window

LVL 22

Expert Comment

ID: 36814289
Alternatively you could use:
SET @home_spread = @home_spread + '.0'
set @home_spread_numeric = cast(@home_spread as numeric(30,2))

Open in new window

LVL 19

Expert Comment

by:Bhavesh Shah
ID: 36814649

I made bit change in SQL.

check out.
declare @home_spread nvarchar(max),@shoe_id int

declare @home_spread_numeric numeric(30,2)

-- the value will look like '+33.5' or '-4.5 or +7'
set @home_spread = (select Replace(Replace(home_team_spread,'+',''),'-','') from schedule_shoe where shoe_id = @shoe_id)

IF IsNumeric(@home_spread)=1
     set @home_spread_numeric = cast(@home_spread as numeric(30,1))

Open in new window


Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql Permission 6 74
SQL Server Degrading on Write 13 72
SSRS report parameters set after publishing to report manager 1 73
SQL Query with Sum and Detail rows 2 62
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below.…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question