Remove numbers from the string

Posted by VIJI Monday, June 29, 2009

About FuncRemoveNumbersFromString SQL User defined function

/* This function takes One argument - String to remove the numbers and returns the trimmed string*/


CREATE FUNCTION dbo.FuncRemoveNumbersFromString (@String as nvarchar(4000)) RETURNS nvarchar(4000)
AS
BEGIN

DECLARE @Result as nvarchar(4000)
DECLARE @Answer as integer
DECLARE @x as integer

set @x = 1
set @Result = ''

while(@x<=len(@String)) begin
set @x = @x + 1
set @Answer = dbo.FuncIsContained(substring(@string,@x-1,1), 'abcdefghijklmnopqrstuvwxyz')
if @Answer = 1 set @Result = @Result + substring(@String,@x-1,1)
end

RETURN @Result
end


/* This function takes two arguments that are varChar() and returns 1*/
/* if the first field is contained in the second field */

CREATE FUNCTION dbo.FuncIsContained(@String varchar(2000),@Base as varChar(50)) RETURNS integer
AS

/* This function returns 1 if each character in @String is contained in @Base */

BEGIN

Declare @i Integer
Declare @Result as Integer

Set @i=1
Set @Result = 1 /* Assume ok */
IF @String = '' AND @Base <> ''
BEGIN
RETURN 0
END
while(@i<=len(@String))

Begin
if charindex(substring(@String,@i,1) ,@Base) =0 begin
Set @Result = 0
End
Set @i = @i +1
End

Return (@Result)
End

USAGE
dbo.FuncRemoveNumbersFromString ('Your String')

Example:
dbo.FuncRemoveNumbersFromString ('Hello123') = Hello

0 Responses to Remove numbers from the string

Post a Comment