• This function will return an int from the beginning of a string.

    SELECT dbo.LeadingNumbers('4568 Lancaster Avenue');

    SELECT *

    FROM Addresses

    WHERE AddressLine like '%Lancaster%'

    AND dbo.LeadingNumbers(AddressLine) >= 4000

    AND dbo.LeadingNumbers(AddressLine) < 5000

    ;

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.LeadingNumbers') AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT'))

    DROP FUNCTION dbo.LeadingNumbers

    GO

    create function dbo.LeadingNumbers

    (

    @in varchar(100)

    )

    RETURNS int

    AS

    BEGIN

    declare

    @out varchar(10),

    @ptr int,

    @len int

    set @len = LEN(@in)

    set @ptr = 1

    while @ptr <= @len

    begin

    if charindex(substring(@in, @ptr, 1),'0123456789') = 0

    goto done

    set @ptr += 1

    end

    done:

    if @ptr = 0

    set @out = ''

    else

    set @out = left(@in, @ptr - 1)

    RETURN cast(@out as int)

    END

    GO