如下面:
需要找出,#c中以#b表中domain为结尾的记录,数据量大希望有比较好的代码:
即找出:
[email protected]
[email protected]
3@@b.comcreate table #c(email varchar(10))
insert into #c values ('[email protected]')
insert into #c values ('[email protected]')
insert into #c values ('3@@b.com')
insert into #c values ('[email protected]')
insert into #c values ('[email protected]')create table #b(domain varchar(10))
insert into #b values('abc.com')
insert into #b values('a.com')
insert into #b values('b.com')select *
from #c
where RIGHT(email,len(email)-CHARINDEX('@',email,1)) in (select domain from #b)
需要找出,#c中以#b表中domain为结尾的记录,数据量大希望有比较好的代码:
即找出:
[email protected]
[email protected]
3@@b.comcreate table #c(email varchar(10))
insert into #c values ('[email protected]')
insert into #c values ('[email protected]')
insert into #c values ('3@@b.com')
insert into #c values ('[email protected]')
insert into #c values ('[email protected]')create table #b(domain varchar(10))
insert into #b values('abc.com')
insert into #b values('a.com')
insert into #b values('b.com')select *
from #c
where RIGHT(email,len(email)-CHARINDEX('@',email,1)) in (select domain from #b)
--create table #c(email varchar(10))
--insert into #c values ('[email protected]')
--insert into #c values ('[email protected]')
--insert into #c values ('3@@b.com')
--insert into #c values ('[email protected]')
--insert into #c values ('[email protected]')
--create table #b(domain varchar(10))
--insert into #b values('abc.com')
--insert into #b values('a.com')
--insert into #b values('b.com')
SELECT *
FROM #c
WHERE EXISTS ( SELECT 1
FROM #b
WHERE REVERSE(SUBSTRING(REVERSE(email), 1,
PATINDEX('%@%', REVERSE(email)) - 1)) = #b.DOMAIN )
/*
email
----------
[email protected]
[email protected]
3@@b.com
(3 行受影响)
*/
insert into #c values ('[email protected]')
insert into #c values ('[email protected]')
insert into #c values ('3@@b.com')
insert into #c values ('[email protected]')
insert into #c values ('[email protected]')
create table #b(domain varchar(10))
insert into #b values('abc.com')
insert into #b values('a.com')
insert into #b values('b.com')
select *
from #c
where exists(select 1 from #b where #c.email like '%@'+domain)
/*
email
[email protected]
[email protected]
3@@b.com
*/
create table #c(email varchar(10))
insert into #c values ('[email protected]')
insert into #c values ('[email protected]')
insert into #c values ('3@@b.com')
insert into #c values ('[email protected]')
insert into #c values ('[email protected]')
insert into #c values ('123@@A.com')
insert into #c values ('[email protected]')
create table #b(domain varchar(10))
insert into #b values('abc.com')
insert into #b values('a.com')
insert into #b values('b.com')
select c.email
from #c c left join #b b on c.email like '%@'+b.domain
where b.domain is not null