Error converting nvarchar to numeric

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Error converting nvarchar to numeric

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-10-04 : 08:08:38

Hi,

I have the following query

SELECT     dbo.TblAuto_coll.Prac_no
FROM         dbo.TblAuto_coll INNER JOIN
                      [GPRD-RCT].dbo.Practice ON dbo.TblAuto_coll.Prac_no = [GPRD-RCT].dbo.Practice.PracticeCode

I know the problem [GPRD-RCT].dbo.Practice.PracticeCode has a datatype as nvarchar. Now, I cant change the data type to numeric as the database is not under my administration.

Is there any way round this ?
N/B : dbo.TblAuto_coll.Prac_no is numeric

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-04 : 08:13:12

how's the numeric value in Prac_no like ?

convert Prac_no to varchar for the JOIN ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-04 : 08:15:35

i hope its having some non numeric values as well which is why it complains while trying to convert to numeric

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-10-04 : 08:30:12

[code]SELECT TOP 100 PracticeCode, *
FROM [GPRD-RCT].dbo.Practice
WHERE (PracticeCode NOT LIKE '%[^0-9]%' OR PracticeCode = '')
AND PracticeCode IS NOT NULL
[/code]
should show you some examples of non-numeric values in that column. They need cleaning up (and if you don't change the datatype of that column the problem will happen again)

Or make a view (restricted to "good" values, and JOIN to that
[code]
CREATE VIEW MyView
AS
SELECT CONVERT(int, PracticeCode) AS intPracticeCode, *
FROM [GPRD-RCT].dbo.Practice
WHERE PracticeCode NOT LIKE '%[^0-9]%'
[/code]
NOTE: Join to intPracticeCode column alias, as that is already cast to Integer and will be more efficient

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-10-04 : 08:57:41

Can I convert the PracticeCode from nvarchar to numeric in the query? how can I do that?

Thanks

Kristen
Test

22859 Posts

Posted - 2011-10-04 : 09:02:36

Not if it isn't a valid number, no.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources