'1' like '[0-9]' - работает!

The PHONE_CLEAN Function
This function will remove all non-numeric characters from a string.


Declare
@Str1 VarChar(max),
@Str2 VarChar(max);
Select
@Str1='(406) 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;

Источник http://www.sqlservercentral.com/articles/T-SQL/63591/