Question on joins with dynamic wildcards

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Question on joins with dynamic wildcards

Author

Topic

igor92128
Starting Member

23 Posts

Posted - 2012-09-06 : 15:58:07

Hello,

Let's say I have an IN statement that goes like this:

select EMP_NUM from my_table mytable where mytable.EMP_NUM in (select EMPNUM from #TempEvent (nolock))

The table EMP_NUM has 100,000 records and #TempEvent has 1,000 records, so the statement should yield 1,000 rows. However, the problem with my_table is that some of the EMP_NUM numbers have extra two numbers at the end, for example:

Ex1:
mytable EMP_NUM value: 8273401
#TempEvent EMPNUM value: 82734

Ex2:
mytable EMP_NUM value: 11123459204
#TempEvent EMPNUM value: 111234592

So in the case above, the two numbers will not match. Is there a way to get these two values to match up? I don't really see how to add a wildcard to the mytable value so that SQL sees all values in that table as 'EMP_NUM%'. Also, EMP_NUM is variable length so substring will not work well. Any ideas?

I have also tried doing something like:

select EMP_NUM
from my_table
join #TempEvent t
on t.EMPNUM = substring(EMP_NUM,1,LEN(EMPNUM))

but this is a painfully slow query and gives extra rows.

Thanks,
Igor

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-09-06 : 16:10:22

if you know how many digits you will have to match on you can replace the '%' with a wildcard '_'. each '_' represents one character

--------------------------
Joins are what RDBMS's do for a living