Error turning Varchar into Numeric

• jamesrichardadams

  Newbie

  Points: 9

  More actions

  December 7, 2010 at 5:08 am

  #227869

  Hi All. I am newish to SQL and I have seen a few similar posts but am still struggling.

  I am basically trying to convert a varchar variable into a number so that I can extract it. It works when I do 1 sector but then when I add another it comes up with the error "Error converting data type varchar to bigint." or "Error converting data type varchar to numeric".

  here are 2 ways I have run the code; (I left out the froms and joins)

  select BSU_BranchCode, BSU_CompanyName, BSU_MarketSector, cast(QR_FreeText as bigint) as Turnover

  where isnumeric(QR_FreeText)=1

  and ((BSU_MarketSector='C') and cast(QR_FreeText as bigint)>100000000)

  or ((BSU_MarketSector='D') and cast(QR_FreeText as bigint)>50000000)

  --or ((BSU_MarketSector='M') and cast(QR_FreeText as bigint)>1000000000)

  --or ((BSU_MarketSector='R') and cast(QR_FreeText as bigint)>75000000)

  group by BSU_BranchCode, BSU_CompanyName, BSU_MarketSector, cast(QR_FreeText as bigint )

  The other way

  declare @turnover2 as numeric (30)

  set @turnover2='QR_FreeText'

  select BSU_BranchCode, BSU_CompanyName, BSU_MarketSector, isnumeric(@turnover2)

  where

  ((BSU_MarketSector='C') and cast(@turnover2 as numeric)>100000000)

  --or ((BSU_MarketSector='D') and cast(QR_FreeText as bigint)>50000000)

  --or ((BSU_MarketSector='M') and cast(QR_FreeText as bigint)>1000000000)

  group by BSU_BranchCode, BSU_CompanyName, BSU_MarketSector

  Any suggestions?

  Thanks.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  December 7, 2010 at 5:27 am

  #1259469

  Hi James

  Firstly, ISNUMERIC() has limitations - it returns true (1) if the value can be converted into a number. SSC has a recent article covering its limitations.

  Secondly, the filters in the expression may be evaluated by SQL Server in any order, not necessarily the logical order which you've carefully calculated should work.

  Thirdly, a few rows of column QR_FreeText should help folks determine the best way to solve this problem. A few rows which should convert, and a few rows which should not.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• LutzM

  SSC Guru

  Points: 107049

  More actions

  December 7, 2010 at 5:40 am

  #1259473

  I'm expecting some non-numeric values in [QR_FreeText] for [BSU_MarketSector]='M'.

  It seems like query optimizer decides to filter on [BSU_MarketSector] [QR_FreeText] first, therewith ignoring the invalid column values of [QR_FreeText].

  The article Chris mentioned can be found here.

  Use the method described in the article above to find the values that cannot be converted into bigint.

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]