declare @matches table (id1 int, id2 int)

Declare @table1 table
(
	id int identity(1,1) not null,
	HospitalName varchar(100),
	Number int
)
Insert Into @table1 Values ('St Marks',29)
Insert Into @table1 Values ('Queen Mary',1023)
Insert Into @table1 Values ('Morrfields',6)

Declare @table2 table
(
	id int identity(1,1) not null,
	range varchar(100),
	Street varchar(100)
)
Insert Into @table2 Values ('3,7,8-12,18-22','Whitely Street')
Insert Into @table2 Values ('1020-1028','Park Crescent')
Insert Into @table2 Values ('9','Old Street South')

Select * From @table1
Select * From @table2

While exists(Select * From @table2 where range like '%,%')
Begin
	Insert Into @matches
	Select A.id, Z.id
	From @table1 as A
	Inner Join 
		(
		Select 
			id,
		 	lEnd = case when charindex('-',range)>0 then convert(int,left(range,charindex('-',range)-1)) else convert(int,range) end,
		 	hEnd = case when charindex('-',range)>0 then convert(int,reverse(left(reverse(range),charindex('-',reverse(range))-1))) else convert(int,range) end
		From 
			(
			Select id, range = left(range,charindex(',',range)-1) From @table2 where range like '%,%'
			) as A
		) as Z
	On A.number between Z.lEnd and Z.hEnd

	Update @table2
	Set 
		range = right(range,len(Range)-charindex(',',range)) 
	From @table2 
	where range like '%,%'
End

	Insert Into @matches
	Select A.id, Z.id
	From @table1 as A
	Inner Join 
		(
		Select 
			id,
		 	lEnd = case when charindex('-',range)>0 then convert(int,left(range,charindex('-',range)-1)) else convert(int,range) end,
		 	hEnd = case when charindex('-',range)>0 then convert(int,reverse(left(reverse(range),charindex('-',reverse(range))-1))) else convert(int,range) end
		From @table2
		) as Z
	On A.number between Z.lEnd and Z.hEnd


Select B.*, C.* 
From @matches as A
Inner Join @table1 as B
On A.id1 = B.id
Inner Join @table2 as C
On A.id2 = C.id

quote:

---

However the ranges come from a Postcode (zipcode) table with more than 2.5 Million rows!! I think a table with individual numbers and 'start number' 'end number' fields could make this number increase closer to 10 Million.

---