Create table

CREATE TABLE [dbo].[tab](

[Name] [varchar](50) NULL

) ON [PRIMARY]

GO

Insert data to table

Insert into [tab] value ''123''

Insert into [tab] value ''abc''

Insert into [tab] value ''ABC''

Insert into [tab] value ''12aa''

Insert into [tab] value ''ax1205''

Insert into [tab] value ''1q2d''

Insert into [tab] value ''b2v2sdfsf331313213''

Create function

CREATE FUNCTION [dbo].[fnReplaceInvalidChars] (@string VARCHAR(300))

RETURNS VARCHAR(300)

BEGIN

    DECLARE @str VARCHAR(300) = @string;

    while PatIndex(''%[^0-9]%'', @str) > 0

    select  @str = Replace(@str, Substring(@str, PatIndex(''%[^0-9]%'', @str), 1), '''')   

    RETURN @str

END

Select and result:

select dbo.fnReplaceInvalidChars(name),* from tab