T-sql on on ip range

Question

Hi

I have two table in a sql databse one has "institutionName, ipAddress" columns and the other one has "ipAdddress, totalHits". I want to join the two table based on ipAddress field, but the problem is the ipAddress in the first table is in a for of "xxx.xxx.xxx.xxx" but in the second table it is in three forms ie "xxx.xxx.xxx.xxx" or "xxx.xxx.." or "xxx.xxx.xxx.*" how can I do this. I am new to T-sql and I really need your help guys

Thnaks

Answer 1

Given table A holds institutionName and table B has totalHits.

Based upon your brief description of the data, it should be possible to strip off repeated dots in the ipAddress field on table B. This will allow you to join using a simple prefix string comparison.

SELECT A.institutionName, B.totalHits
FROM A JOIN B ON A.ipAddress LIKE REPLACE(B.ipAddress, '..', '') + '%'