Solved

error converting varchar to float - how to bypass bad data

Posted on 2011-03-21
11
318 Views
Last Modified: 2012-05-11
Hi,

Im performing some calcuations within my select query.
Every now n then one of the values isnt entered correctly and is a varchar - this then returns an error from the stored proc and crashes my report.


heres the part of the query thats the problem:
SELECT
                  dbo.tbPurchaseItem.mPrice * dbo.tbPurchaseItem.iQty - dbo.tbPurchaseItem.mPrice * dbo.tbPurchaseItem.iQty * (convert(float,dbo.tbPurchaseItem.vCustom1)  /100) AS [cost inc discount],

dbo.tbPurchaseItem.vCustom1 was entered as 7,5 and crashed the report.
There are now checks to enure this wont happen again, however for older orders this may happen again..

Any ideas how to put a default value in or somehow change the varchar to float,

Thanks
                  
0
Comment
Question by:lgreally
  • 4
  • 3
  • 2
  • +2
11 Comments
 

Expert Comment

by:JoelDev
Comment Utility
In SQL Server, you can use the built in function called ISNUMERIC() to check to see if a field is a number or a varchar.

You could either run an update query and set the ones that aren't numeric to zero or you could do a SELECT CASE statement right in your current query to get around data that isn't a number.
0
 

Author Comment

by:lgreally
Comment Utility
Thanks Joel, I tried
case dbo.tbPurchaseItem.vCustom1
when isnumeric(dbo.tbPurchaseItem.vCustom1) then 'num'      
else 'not num'
end,

to test your response, however I still get
Syntax error converting the varchar value '7,5' to a column of data type int.

any ideas?
0
 
LVL 14

Accepted Solution

by:
Christopher Gordon earned 83 total points
Comment Utility
"Any ideas how to put a default value in or somehow change the varchar to float"

That's tough.  What should 7,5 translate too?  7.5?  75?

Something like this would turn the value null it is not numeric based on the above recommendation...

select nullif(ISNUMERIC([yourFieldName),0)

You could use a function that replaces all non numeric values but it may not give you the results your looking for based on the question above (Is it 7.5 or 75?).  For example the below function will render a value that will translate to float, but may not be the number your looking for.


create function [dbo].[ParseNumericCharacters](@input_string varchar(200))
returns varchar(200)


as

begin

      declare @output_string varchar(200)=''
      declare @index int
      declare @ascii_code int
 
      set @output_string = ''
      set @index = 1
 
 
      --traverse input string
      while @index <= len(@input_string)
      begin
                 
            --get current character in string
            set @ascii_code = ascii(substring(@input_string, @index, 1))
       
            if (@ascii_code between 48 and 57)
            begin  
                       
                        set @output_string = @output_string + substring(@input_string, @index, 1)
                                         
            end
           
            set @index = @index + 1
           
      end
 
      return @output_string
end

GO

0
 

Author Comment

by:lgreally
Comment Utility
thanks gohord
Using isnumeric it converst everything to 0 as the field is a text field
its just that when its not a number converting to float crashes

so I suppose I either need to change , to .
or change the value to 0 if its not a value that can be converted??
0
 
LVL 14

Assisted Solution

by:Christopher Gordon
Christopher Gordon earned 83 total points
Comment Utility
Did you check to see if there is a large number of results in your data that cause the crash?

select * from dbo.tbPurchaseItem.vCustom1 where  isnumeric(dbo.tbPurchaseItem.vCustom1) = 0

If it's just that one record would it be possible to fix the data?

Good luck
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:lgreally
Comment Utility
yes thats what Ive done, there was just 1 record.
I wanted to know if/how I could do this in case it somehow happens again (as an extra precaution)

cheers
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Hi.  I think the first suggestion is a good solution...the issue is in the syntax for the case statement.  You want something like this:
case isnumeric(vCustom1) when 1 then 'num' else 'not num' end

Open in new window

0
 
LVL 2

Assisted Solution

by:CoolBurn28
CoolBurn28 earned 42 total points
Comment Utility
hi
gohord has  a nice function it would help you a lot...
Just an additional for his code on ascii
alter function fn_test(@input_string varchar(200))
returns varchar(200)
as
begin
      declare @output_string varchar(200)
      declare @index int
      declare @ascii_code int

      set @output_string = ''
      set @index = 1
      --traverse input string
      while @index <= len(@input_string) 
      begin
                  
            --get current character in string
            set @ascii_code = ascii(substring(@input_string, @index, 1))
            if (@ascii_code between 48 and 57) [b]or (@ascii_code = 46)[/b]
            begin   
                        set @output_string = @output_string + substring(@input_string, @index, 1)                      
            end
            set @index = @index + 1
      end
      return @output_string
end
go

select db_test.dbo.fn_test('7.5') as test

Open in new window


to include decimal points.. cheers
0
 
LVL 14

Expert Comment

by:Christopher Gordon
Comment Utility
Nice addition CoolBurn28...  I"ll be incorporating your addition in my use of that function from now on.
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Nice function.  What happens when you have a value like 7.5.2 in your field?
I would stick with isnumeric in this case as you want a number after and not a string...but that is just me.  The 'num' | 'not num' was just an example, imagine code like this:
convert(float,
        case isnumeric(vCustom1)
           when 1 then vCustom1
           else 0
        end
) as fltCustom1

Open in new window

0
 

Author Closing Comment

by:lgreally
Comment Utility
Thanks for your help.
The comments were useful
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
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.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now