how to i remove characheters from the prefix table and make sure only from the start of string are removed

I need to be able to strip the following prefixes from product codes as you see I have included a simple query while yes the below shows me the cm im not wanting i cant use replace as it code replace a开发者_运维技巧ny instance of cm the prefixes are held in the supplire table cross refer with the products table

prefixes are not always two chrachters for example can be TOW

SELECT *  , left(prod.productcode, LEN(sup.prefix)) AS MyTrimmedColumn
FROM MSLStore1_Products prod  ,supplier sup
WHERE prod.suppid = 9039 AND prod.SgpID = 171
and sup.supno = prod.suppid

Product Codes:

ProductCode
CMDI25L
CMDI300M
CMDI750M
CMXFFP5L

Prefixes:

Prefix
CM
CM
CM
CM

You could use SUBSTRING() for that:

SUBSTRING(prod.productcode, LEN(sup.prefix))

(concrete syntax may vary for different database-managers)

You might want to put your prefixes in another table, and run the productCode field through a filtering UDF to retrieve the final code.

Something like

SELECT * , dbo.FilterPCode(prod.productcode) AS MyTrimmedColumn FROM MSLStore1_Products prod ,supplier sup WHERE prod.suppid = 9039 AND prod.SgpID = 171 and sup.supno = prod.suppid

then just define a UDF that takes a string product code and removes any prefix from the front.

You might see a performance hit using a UDF like this for really massive queries, and if that's the case, it might be better to generate a table of table of product codes without the prefix linked to the product codes with the prefix. Don't know enough about the data schema to know if that's truly possible without getting cross links though.

how do i thought use this query and imortant the results from that into the products table external code field

 SELECT *  , right(prod.productcode, len(prod.productcode) - LEN(sup.prefix) ) AS ExternalCoode
 FROM MSLStore1_Products prod  ,supplier sup
 WHERE prod.suppid = 9217 AND prod.SgpID = 123 and sup.supno = prod.suppid