asked on

Is it possible to write a T-SQL for this?

Dear Gurus,

The objective of this SQL statement is to find potential duplicate values by comparing the list of accountnumbers(given by a manager) with the accountnumbers in "table1".

Table1
_______
82244567
00082244567
93466666
123456
0088888888
88888888

Given values(by a manager) that we need to compare:
______________
82244567
88888888

My current T-SQL:
______________
*******************************************************
Select *
from table1
where acctnumber like '%82244567'

Result:
_______
82244567
00082244567
********************************************************

Select *
from table1
where acctnumber like '%88888888'

Result:
______
0088888888
88888888

*********************************************************

As can be seen above, I have to execute the same Sql statement for each account number in the given list inorder to find a potential duplicate.

Is there a way that I can handle all the given accounts in a single SQL and find the duplicate values?
something like,

Select *
from table1
where acctnumber like in ( '%88888888', '%82244567')

Please advise. Thanks for your help in advance.

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vdesai_8

ASKER

That would work, but only for this scenario. How about if the list given to me by my manager has 60 accounts. I would not want to write it 60 times.

"where acctnumber like '%88888888'
OR acctnumber like '%82244567'
or acctnumber like '%3'
or acctnumber like '%4'
or acctnumber like '%5'
or acctnumber like '%6'
...
...
or acctnumber like '%60'

Is there some other way to do it?

Thanks!

Guy Hengel [angelIII / a3]

yes, there are other ways to do it, but even longer ones :)

Select * 
from table1 t1
join ( select '%88888888' match_value
     union all select '%82244567'
     union all select '%1'
     ) l
on t1.acctnumber like l.match_value

Open in new window

ASKER CERTIFIED SOLUTION

bashka_abdyli

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bashka_abdyli

This is if you have values in some table :

Select * 
from table1 t1
join ( select '%' + match_value from x) l
on t1.acctnumber like l.match_value

Open in new window

bashka_abdyli

with alias

Select * 
from table1 t1
join ( select '%' + match_value match_value1  from x) l
on t1.acctnumber like l.match_value1

Open in new window

vdesai_8

ASKER

Thanks!