modify sql function, delete first character in string

Question

Hi, I want to modify the following "extract numbers" function below to check if the first number in the output is "1", if so, delete the the "1" before displaying the output. THanks!

Ex.

Input: QW 1 RT 309
Output: 309

create

function [dbo].[ExtractNumbers](@Numbers nvarchar(2000)) 
returns

nvarchar(2000) 
as

BEGIN

declare

@NonNumericIndex int 
set

@NonNumericIndex = PATINDEX('%[^0-9]%',@Numbers) 
WHILE

@NonNumericIndex > 0 
begin



SET

@Numbers = REPLACE(@Numbers,SUBSTRING(@Numbers,@NonNumericIndex,1),'') 
SET

@NonNumericIndex = PATINDEX('%[^0-9]%',@Numbers) 

SET

end



return

@Numbers 
END

Answer 1

create function [dbo].[ExtractNumbers](@Numbers varchar(2000)) returns nvarchar(2000) as  
begin
   declare @NonNumericIndex int 
   set @NonNumericIndex = patindex('%[^0-9]%',@Numbers) 
   while @NonNumericIndex > 0 
   begin
      set @Numbers = replace(@Numbers,SUBSTRING(@Numbers,@NonNumericIndex,1),'') 
      set @NonNumericIndex = PATINDEX('%[^0-9]%',@Numbers) 
   end
   if left(@Numbers, 1) <> '1'
      set @Numbers = right(@Numbers, len(@Numbers) - 1)
   return @Numbers
end