Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Error turning Varchar into Numeric


Error turning Varchar into Numeric

Author
Message
jamesrichardadams
jamesrichardadams
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 3
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
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8975 Visits: 19028
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
Exploring Recursive CTEs by Example Dwain Camps
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7001 Visits: 13559
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search