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.
USE [DBNAME]
GO
/****** Object: UserDefinedFunction [dbo].[fn_filternumbers] Script Date: 11/27/2009 17:59:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fn_filternumbers]
(
@Src nvarchar(255)
)
RETURNS nvarchar(255)
AS
BEGIN
declare @Res nvarchar(255)
declare @i int, @l int, @c char
select @i=1, @l=len(@Src)
SET @Res = ''
while @i<=@l
begin
set @c=upper(substring(@Src,@i,1))
IF @c IN ('0','1','2','3','4','5','6','7','8','9')
SET @Res = @Res + @c
set @i=@i+1
end
return(@res)
END
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.
