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.
| Author |
Topic |
|
igor92128
Starting Member
23 Posts |
 Posted - 2012-08-28 : 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: 82734So 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
38200 Posts |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-08-28 : 19:50:43
|
| How about:select ZIP_CODE from my_table mytable join #TempEvent ton 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 - 2012-08-28 : 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. |
|
|
|
|
|
|
String wildcard matching in a join