Storing telephone as numeric is not recommended, however, you may want to try
select tel, cast(F.NewTel as Numeric(15)) as NewTel from Site
CROSS APPLY (select replace(Tel,'-','') as NewTel) F
where ISNUMERIC(F.NewTel) = 1
>I have a Tel field defined as Char(12). If I do this command:
>
>
>SELECT Tel,CAST(Tel AS Numeric) FROM Site
>
>
>I get:
>
>"Error converting data type varchar to numeric."
>
>Basically, I have a telephone field that I want to take its data and replace that into a Numeric(15) field which already exists. All telephone numbers have a 506-111-2222 format. The example above is just a short SQL command I was trying to execute as using Str() and concatening the 1-3, 5-7 and 9-12 characters into a numeric value did not do.
If it's not broken, fix it until it is.
My Blog