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
Solved

error converting varchar to float - how to bypass bad data

Posted on 2011-03-21
11
323 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 83 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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 83 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 59

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 42 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 59

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
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…

790 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