TSQL – Retrieve only numbers in a string

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.

PermaLink

2 comments:

Anonymous said...
This comment has been removed by a blog administrator.
Anonymous said...
This comment has been removed by a blog administrator.
 
Blog Directory - OnToplist.com