--将公司内部数据每隔3位加'.'，去掉最后一个'.'
--替换里面的'0'为空，和上海的作比较！

------解决方案--------------------
SQL code

create table t1(id varchar(100))
insert into t1
select '58.78.22.69 58.78.22.110' union all
select '59.28.2.169 59.28.2.245' union all
select '158.78.22.9 158.78.22.78' union all
select '169.8.22.69 169.8.22.129' union all
select '192.168.22.69 192.168.22.209' union all
select '127.0.0.1 127.0.0.110'
go

create table t2(id varchar(100))
insert into t2
select '058078022069 058078022110' union all
select '158078022009 158078022078' union all
select '169008022069 169008022129' union all
select '127000000001 127000000110'
go

create function f_str(@a varchar(100))
returns varchar(100)
as
begin
declare @id int
declare @s1 varchar(100)
declare @s2 varchar(100)
declare @s1g varchar(100)
declare @s2g varchar(100)
set @id = 1
set @s1 = ''
set @s2 = ''
set @s1g = ''
set @s2g = ''
set @s1 = substring(@a,1,charindex(' ',@a)-1)
set @s2 = substring(@a,charindex(' ',@a)+1,len(@a))
while(@id <= 4)
    begin
        set @s1g = @s1g +'.'+ ltrim(cast(substring(@s1,1,3) as int))
        set @s1 = substring(@s1,4,len(@s1))
        set @s2g = @s2g +'.'+ ltrim(cast(substring(@s2,1,3) as int))
        set @s2 = substring(@s2,4,len(@s2))
        set @id = @id + 1
    end
set @a = stuff(@s1g,1,1,'')+' '+stuff(@s2g,1,1,'')
return @a
end
go

select id
from t1
where id not in (select dbo.f_str(id)id from t2)

drop function f_str
drop table t1,t2

id
----------------------------------
59.28.2.169 59.28.2.245
192.168.22.69 192.168.22.209

(2 行受影响)