help for the script

[22strider]

Hello Friends

I was wondering if anyone could help me with the following script. I've list of item numbers. Some items have unwanted prefix attached to them. I have list of unwanted prefixes in a saperate table. I wrote following script. It works for removing the prefixes but it also is giving me item number with prefix in a saperate record. So some of the records are repeating. Following is the script and I am also uploading sample result. I am changing the table names and fields for the sake of simiplicity:
Script:
SELECT DISTINCT [Table1].[Item Number],

IIf(([Table1].[Item Number] Like PrefixQAD.[Item Number] & "*"),Mid([Table1].[Item Number],InStr(1,[Table1].[Item Number],"-")+1,Len([Table1].[Item Number])),[Table1].[Item Number]) AS PrefixRemoved,

FROM [Table1], PrefixQAD;

Following is the result sample:

Item Number Prefix removed
pre-12345 pre-12345
pre-12345 12345


the first record in the result table is an unwanted record. I can't figure out what is causing this record to appear in the result.

Thanks for your time

Rajesh

 

[xenou]

I think you are just selecting both "before" and "after" in your selection (?). Is this any different when you only select the cleaned up results:

SELECT DISTINCT [COLOR="RoyalBlue"][Table1].[Item Number][/COLOR], [COLOR="SeaGreen"]IIf(xxx) AS PrefixRemoved[/COLOR]

Try instead:

SELECT DISTINCT [COLOR="SeaGreen"]IIf(xxx) AS PrefixRemoved[/COLOR]

How many prefixes do you have in that table of unwanted prefixes?

 

[xenou]

Hmm, totally unsure of my last post but here's another shot. Maybe we don't need that cross join if the hyphen's are the marker? This would of course fail if other item numbers did have legitimate hyphens in them (pre-12345-6789)

SELECT DISTINCT 
    IIF(Instr(1,[Table1].[Item Number],"-"),
    Right([Table1].[Item Number],Len([Table1].[Item Number])-InStr(1,[Table1].[Item Number],"-")),
    [Table1].[Item Number])
FROM
    Table1

 

[22strider]

There is a list of prefixes that I have in the table PrefixQAD. There is a dash (-) between prefix and the item number. But the dash may be a legitimate part of the item number as well. I am able to remove the prefixes by using the script that I gave in my post. But for some reason I am also getting result with the prefix in the result records (the 1st record in my example).
I don't understan what is making this record appear and how to stop it from appearing.

 

[xenou]

Well, cross joins are sometimes difficult to control. It's probably that you are crossing not only matches but also non-matches against all records in both tables. You need a real join, or a where. What do you intend to do with the results of this query? What are the prefixes like? How big is Table1? Is there anything common to all the prefixes? Or is it all just randomness and troublesomeness?

 

[22strider]

Not sure how I can get a real join. I just have a list of prefixes that should be removed from the item numbers. For example an item number may look like "TP-12345TEST" another item number may be "XY-45678TEST2". Now, if "TP" is in the list of prefixes and "Y" is not then only item number with prefix "TP" should be stripped of the prefix. The table one has about 60,000+ records and is growing.
The goal is to strip prefix from the item numbers that have prefixes matching with the list (list of prefixes) given in table PrefixQAD.

 

[xenou]

Okay,
One point I'm unclear of is that your SELECT query doesn't actually "strip" the prefixes. Do you want to actually change/update the values in the table or just get a query result set? What will you do with your SELECT query (which is only item numbers)? Sorry to be a fly about this one. Thing is, you could probably run a correlated query or create some kind of IN clause, but with many records and many prefixes it may get slow over time - it's a lot of text comparison and manipulation that has to be run for each item number and against all the prefixes. Is it possible to control the entry of new records when they are inserted? Can you create a field in Table1 for "cleaned up item number"? Do the prefixes always have a hyphen as the last character of the prefix?

 

[xenou]

This is my latest effort. Keep relating more about your requirement here as there may be better:

SELECT IIF(Instr(1, t1.[Item Number], "-") > 0,
        Right(t1.[Item Number],Len(t1.[Item Number]) - Instr(1, t1.[Item Number], "-")),
        "") AS ItemNumber_Clean, t1.[Item Number] 
    FROM Table1 t1
    INNER JOIN PrefixQAD t2
    ON 
    Left(t1.[Item Number], Instr(1, t1.[Item Number], "-")) = t2.[Item Number]

UNION

    SELECT t1.[Item Number] As ItemNumber_Clean, t1.[Item Number]
    FROM Table1 t1
    LEFT JOIN PrefixQAD t2
    ON Left(t1.[Item Number], Instr(1,t1.[Item Number], "-")) = t2.[Item Number]
    WHERE t2.[Item Number] Is Null