Parse a fixed length numeric value from a string

if exists (select * from dbo.sysobjects where id = object_id(
N'[dbo].[fn_ParseStringNumberInt]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_ParseStringNumberInt]
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE  FUNCTION fn_ParseStringNumberInt 
(
@String VARCHAR(255),
@intDigits int
)
RETURNS int AS

/* Description: Parses out a intDigits number from a string. 
 * Inputs:  @String VARCHAR(255): The String containing the number
 *    @intDigits: Number of digits for number to be parsed
 * Return: int
 * Examples: 
 *@String = 'this should be zip code 75007 or 11111111'
 * @intDigits = 5
 * returns 75007
 *@String = 'this should be zip code 11111111 or 75007'
 * @intDigits = 5
 * returns 75007
 * @String = 'this should  be zip code 11111 or 75007 zip'
 * @intDigits = 5
 * returns 11111
 */
BEGIN
declare @intCurrPos int
declare @strWorkNumber varchar(255)
declare @intWorkNumber int
declare @intEndString int

set @intCurrPos = 0
set @intEndString = Len(@String)
set @strWorkNumber = ''
set @intWorkNumber = -1

while @intCurrPos <= @intEndString
begin
set @intCurrPos =+ @intCurrPos  + 1
if isnumeric(Substring(@String, @intCurrPos,1))=1
set @strWorkNumber = @strWorkNumber + Cast(Substring(@String, @intCurrPos, 1) as VARCHAR(1))
else
--this is non-numeric
if Len(@strWorkNumber) <= 0
CONTINUE
else 
if Len(@strWorkNumber)<> @intDigits 
begin
set @strWorkNumber = ''
CONTINUE
end
else
BREAK
end
if len(@strWorkNumber) > 0 
begin
set @intWorkNumber = cast(@strWorkNumber as integer) 
end

RETURN @intWorkNumber
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXEC ON fn_ParseStringNumberInt TO PUBLIC