I need to add 4 zeros to the start of all exsisting item lookup codes. Would anyone know a sql query to achieve this?
*This post is locked for comments
Thanks worked perfect
My SQL Server 2005 is a little rusty, but I think he plus sign (+) acts as a concatenate operator in SS2005;
First try this
SELECT ITEMLOOKUPCODE, '0000'+ITEMLOOKUPCODE AS NEW_ILC
FROM ITEM
If it looks good, you can do an update - backup your database first
UPDATE ITEM
SET ITEMLOOKUPCODE = '0000'+ITEMLOOKUPCODE
Would also suggest you backup your db, restore as a "testing" database, and run the update statement on your test db first, in case you have ILCs that are less than four characters from the limit of the ILC field.
Another option is to keep your ILC but insert the ILC with the four zeroes as an ALIAS
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 290,902 Super User 2024 Season 2
Martin Dráb 229,297 Most Valuable Professional
nmaenpaa 101,156