select *
from thangela.NpowerNorthern_v1 a INNER JOIN EPPS.dbo.Customers b ON b.MPANCORE = CAST(a.MPAN1 as VARCHAR)

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

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

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

You can check my BLOG here

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

DECLARE @Foo VARCHAR(50)

SET @Foo = '1e1'

-- Is numeric
SELECT ISNUMERIC(@Foo)

-- Conversion fails
SELECT CAST(@Foo AS NUMERIC(17,2))

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

You can check my BLOG here