Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

error converting varchar to float - how to bypass bad data

Posted on 2011-03-21
11
Medium Priority
?
337 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
ID: 35185658
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
ID: 35185819
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 249 total points
ID: 35185833
"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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:lgreally
ID: 35185923
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 249 total points
ID: 35185948
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
 

Author Comment

by:lgreally
ID: 35186027
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 60

Expert Comment

by:Kevin Cross
ID: 35186083
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 126 total points
ID: 35186235
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
ID: 35188941
Nice addition CoolBurn28...  I"ll be incorporating your addition in my use of that function from now on.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35190213
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
ID: 35405753
Thanks for your help.
The comments were useful
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Screencast - Getting to Know the Pipeline

824 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