bits and bytes of my life experiences
With related to my earlier post which demonstrated how to retrieve only numbers in a string using C#, this is how you can do it using TSQL in SQL Server. This will be also become handy if you want to do this in a backend process (SP, Trigger, etc). This is a UDF which you can reuse in your TSQL codes.
/****** Object: UserDefinedFunction [dbo].[fn_filternumbers] Script Date: 11/27/2009 17:59:56 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER FUNCTION [dbo].[fn_filternumbers]
declare @Res nvarchar(255)
declare @i int, @l int, @c char
select @i=1, @l=len(@Src)
SET @Res = ''
IF @c IN ('0','1','2','3','4','5','6','7','8','9')
SET @Res = @Res + @c
You can use this function in the following manner;
select telephone, dbo.fn_filternumbers(telephone) from users
This will return telephone numbers by removing white spaces, characters and will only return numbers. (i.e +94 (475) 849 984 would return 94475849984)
Please feel free to drop a comment if you have any question on this.