Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

error converting varchar to float - how to bypass bad data

Posted on 2011-03-21
11
Medium Priority
?
334 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
[X]
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
  • 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
Independent Software Vendors: 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!

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

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…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

688 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