>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 got confused with samples I saw on the net where many are using Str() to extract a portion of a string. When I used Substring(), it worked:
UPDATE Site SET [Tel-Temporary]=CONVERT(Numeric,SUBSTRING(Tel,1,3)+SUBSTRING(Tel,5,3)+SUBSTRING(Tel,9,4))We store that as numeric for indexing performance, search, etc, where the user simply enter the digits, which includes the regional code, and we have a framework specific telephone number search which will grab everything greater than what is being entered. This works extremely fast and avoids all the special non conventional ways of data entering each user will use.