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