| Author |
 Topic  |
|
|
igor92128
Starting Member
23 Posts |
 Posted - 08/28/2012 : 19:02:46
|
Hello,
Let's say I have an IN statement that goes like this:
select ZIP_CODE from my_table mytable where mytable.ZIP CODE in (select ZIP from #TempEvent (nolock))
The table ZIP_CODE has 100,000 records and #TempEvent has 1,000 records, so the statement should yield 1,000. However, the problem with my_table is that some of the ZIP codes have extra numbers at the end, for example:
mytable value: 82734-3922
#TempEvent value: 82734
So in the case above, the it 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 'ZIP_CODE%'. Any ideas?
Thanks,
Igor |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
chadmat
The Chadinator
USA
1961 Posts |
Posted - 08/28/2012 : 19:50:43
|
How about:
select ZIP_CODE
from my_table mytable
join #TempEvent t
on t.ZIP = substring(ZIP_CODE, 1, 5)
Or create a computed column that is substring of ZIP_CODE so you can index it.
-Chad |
 |
|
|
igor92128
Starting Member
23 Posts |
Posted - 08/28/2012 : 19:59:18
|
| Chad, the substring code works better and I am not seeing the blank records. What SQL function is used to compute the length of the substring? My ZIP code table has different length ZIP codes. |
|
|
| |
Topic |
|
String wildcard matching in a join
