• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

error converting varchar to float - how to bypass bad data

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
lgreally
Asked:
lgreally
  • 4
  • 3
  • 2
  • +2
3 Solutions
 
JoelDevCommented:
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
 
lgreallyAuthor Commented:
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
 
Christopher GordonSenior Developer AnalystCommented:
"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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
lgreallyAuthor Commented:
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
 
Christopher GordonSenior Developer AnalystCommented:
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
 
lgreallyAuthor Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
CoolBurn28Commented:
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
 
Christopher GordonSenior Developer AnalystCommented:
Nice addition CoolBurn28...  I"ll be incorporating your addition in my use of that function from now on.
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
lgreallyAuthor Commented:
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.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now