Posted on 2012-03-14
I need to delete row in table A (Price Code File, OEPRCFIL)when there is not a match in Table B (Item Master file, IMItmIdx). The field in OEPrcFil that contains the item number is Filler_0001. If Filler_0001 ONLY contained the item number, the TSL would be:
(I will first build a Select rather than a Delete and verify the records)
Select Filler_0001 From OEPrcFil
WHere Filler_0001 Not in (Select Item_No from IMItmIdx)
The above works with the provision that Filler_0001 contains only the Item number. The problem I have is that the Filler_0001 contains data in addition to the Item Number. Specifically, Filler_0001 contains the Vendor Number with the Item Number concatonated onto the end, e.g. VENDORNOITEMNO = ACMEPRODUCTA or GIANTPRODUCTA.
As you can see the Vendor Number that prepends the Item Number is not a fixed length. Since it is not a fixed length, that preludes identifying the Item Number with Substring.
I need some sort of pattern matching. I have looked at CHARINDEX and PATINDEX but I was not successful in using a SELECT inside those functions.
I am using SQL 2000.
What TSQL syntax would I use to select all the OEPrdFil Records that do not have an item number match in the Item Master table?