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
精彩评论