Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    3

    Question Unanswered: Converting varchar to int (or numeric)

    In one of my stored procedures, a varchar is input that is assumed to be an int, and I need to validate that it is an int before I CAST it as an int. I'm currently using ISNUMERIC() to eliminate non-numeric values, but how do I recover gracefully if values such as '7.5e3', '$334', or '45.9943' sneak through? They all pass the ISNUMERIC() test, but cause errors when being cast to int. Similar problems if casting to numeric, etc.

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    You can use the case statement - for example:

    declare @test varchar(20)
    select @test='123.3'
    select case when charindex('.',@test) > 0 then cast(@test as decimal(10,2))
    when charindex('e',@test) > 0 then cast...
    else cast(@test as int) end

  3. #3
    Join Date
    Dec 2003
    Posts
    3

    Smile

    Thanks. That should work. On retrospect, it's obvious enough I should have come up with that myself!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •