Cleaning Phone Numbers using CTE

Cleaning Phone Numbers using the Common Table Expressions

Declare
@Str1 VarChar(max),
@Str2 VarChar(max);
Select
@Str1='(208)*/ 555-1212',
@Str2='';

With PhoneClean as
(
Select
 Case
  when SubString(@Str1,1,1) like '[0-9]' then SubString(@Str1,1,1)
  else ''
 End[Chr],
 1[Idx]
Union All
Select
 Case
  when SubString(@Str1,Idx+1,1) like '[0-9]' then SubString(@Str1,Idx+1,1)
  else ''
 End,
 Idx+1
from PhoneClean
where (Idx+1)<=Len(@Str1) ) Select  @Str2=@Str2+Chr from PhoneClean option (MaxRecursion 0); Select  @Str2; 

Source:www.sqlservercentral.com