SQL Help : Cleaning up Item Lookup Codes

(0) Share

Report

Posted on by Taier P

908

Hi, All! I'm only halfway through SQL For Dummies, and am hoping someone can help me with a SQL query to clean up a data entry issue.

Somewhere along the line someone created hundreds of products with an extra hyphen at the end of the item lookup code, which has been a pain when manually typing in lookup codes.

Does anyone out there have the SQL skills to write a query that would look at the Item Lookup Code, check to see if it ends in a hyphen, and it it does, remove that last hyphen without removing any other hyphen in the lookup code?

Any help is greatly appreciated.

*This post is locked for comments

All responses (4)

Answers (1)

Community Member on at

Like (0)

Report

RE: SQL Help : Cleaning up Item Lookup Codes

But if u r in HQ environment, u will need to make sure to replicate the changes to stores, so u should amend the above statement to change the LastUpdated field and therefore u can create an "Update Inventory" worksheet using the "recently changed" option

UPDATE ITEM

SET ITEMLOOKUPCODE=LEFT(ITEMLOOKUPCODE,LEN(ITEMLOOKUPCODE)-1)

,LastUpdated=getdate()

WHERE RIGHT(ITEMLOOKUPCODE,1)='-'
Community Member on at

Like (0)

Report

RE: SQL Help : Cleaning up Item Lookup Codes

No problem
Taier P 908 on at

Like (0)

Report

RE: SQL Help : Cleaning up Item Lookup Codes

Thank you VERY much!!!
Verified answer

Community Member on at

Like (0)

Report

RE: SQL Help : Cleaning up Item Lookup Codes

UPDATE ITEM SET ITEMLOOKUPCODE=LEFT(ITEMLOOKUPCODE,LEN(ITEMLOOKUPCODE)-1) WHERE RIGHT(ITEMLOOKUPCODE,1)='-'