April 28, 2009 at 7:43 am
Hi Guys,
This should be easy for some of you - I am looking at SQL Query which will convert succesfully varchar to numeric.
I am using the query below which should do the trick:
Select *
from Table1 a INNER JOIN Table2 b ON Convert(numeric(17,2), b.Col1) = a.Col2
The column b.Col1 is a varchar and a.Col2 is numeric.
When I run this query it gives me an error
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
Please correct what I might be doign wrong.
Cheers
Arun
April 28, 2009 at 7:48 am
are you sure that all your varchar values are numerically compatebly?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 28, 2009 at 7:50 am
this might work, by converting the numeric to varchar instead.
select *
from thangela.NpowerNorthern_v1 a INNER JOIN EPPS.dbo.Customers b ON b.MPANCORE = CAST(a.MPAN1 as VARCHAR)
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 28, 2009 at 12:35 pm
It looks like you have some non numeric data in your column. Maybe this would help:select
*
from
thangela.NpowerNorthern_v1 a
INNER JOIN
(
SELECT
--Columns needed,
CAST( b.MPANCORE AS numeric(17,2) AS MPANCORE
FROM
EPPS.dbo.Customers b
WHERE
b.MPANCORE NOT LIKE '%[^0-9.-]%'
) AS T
ON T.MPANCORE = a.MPAN1 EDIT - I didn't read your post correctly. I though you were doing a calculation. So I changed the code.
April 29, 2009 at 4:38 am
Brilliant both the queries works.
thanks guys!
Keep the good work of sharing knowledge.
April 29, 2009 at 5:09 am
if you are willing to ignore records with non-numeric value in MPANCORE,
try this
select *
from thangela.NpowerNorthern_v1 a
INNER JOIN EPPS.dbo.Customers b ON case when IsNumeric(b.MPANCORE) = 1 then b.MPANCORE else -1 end = a.MPAN1
-- Asuming -1 will not be the valid value for a.MPAN1
April 29, 2009 at 2:20 pm
Atif Sheikh (4/29/2009)
if you are willing to ignore records with non-numeric value in MPANCORE,try this
select *
from thangela.NpowerNorthern_v1 a
INNER JOIN EPPS.dbo.Customers b ON case when IsNumeric(b.MPANCORE) = 1 then b.MPANCORE else -1 end = a.MPAN1
-- Asuming -1 will not be the valid value for a.MPAN1
That might work, but you need to be carfull using SQL's "IS" functions.. DECLARE @Foo VARCHAR(50)
SET @Foo = '1e1'
-- Is numeric
SELECT ISNUMERIC(@Foo)
-- Conversion fails
SELECT CAST(@Foo AS NUMERIC(17,2))
April 29, 2009 at 9:28 pm
Thats right. And thanks for pointing.
Also, '1d1' is a Numeric Value but generate error on conversion. The values are valid from '1d1' - '1d308' (if I am not wrong).
But it will be a highly exceptional case to have one occurence of d or e within the range. But, still, the point is valid.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply