0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for programmers

declare @address table (street varchar(50), housenrext varchar(15));
insert @address 
          select '1st street', '22'
union all select '1st street', '22a'
union all select '1st street', '24'
union all select '1st street', '33'
union all select '1st street', '112a';

declare @region table (name varchar(50), street varchar(50), startnr int, endnr int);
insert @region
          select 'Region 1', '1st street', 1, 30
union all select 'Region 2', '1st street', 31, 40
union all select 'Region 3', '1st street', 41, 200;

select  *
from    (
        select  cast(case 
                when patindex('%[^0-9]%', housenrext) = 0 then housenrext
                else substring(housenrext, 1, patindex('%[^0-9]%', housenrext) - 1)
                end as int) as housenr
        ,       street
        from    @address
        ) as addr
left join
        @region region
on      addr.street = region.street
        and addr.housenr between region.startnr and region.endnr

fork query

created dec 8 11 jon.doe6270