SQL Server Replace Function
Hello, i am looking for sql server equivalent function for this ...
Could some one help me ...
[php]function removealphas (byval alphanum as variant)
dim clean as string dim pos, a_char$ pos = 1
if isnull(alphanum) then
exit function f
or pos = 1 to len(alphanum)
a_char$ = mid(alphanum, pos, 1)
if a_char$ >= "0" and a_char$ <= "9"
then clean$ = clean$ + a_char$
end if
next
pos removealphas = clean$
end function [/php] thanks, suji
Re: SQL Server Replace Function
CREATE FUNCTION dbo.REMOVEALPHA
(@str VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
declare @validchars varchar(8000)
set @validchars = '0-9'
WHILE PATINDEX('%[^' + @validchars + ']%',@str) > 0
SET @str=REPLACE(@str, SUBSTRING(@str ,PATINDEX('%[^'
+ @validchars +']%',@str), 1) ,'')
RETURN @str
END
GO
Execuete:
SELECT dbo.REMOVEALPHA('REPLACEAPHA1111122')