Hi I have an Access query (below) that I'm trying to recreate in SQL Server:
UPDATE tblProducts SET tblProducts.ProductCode = [tblProducts].[ProductPrefix] &
Format([tblProducts].[ProductID],"00000")
WHERE (((tblProducts.ProductCode) Is Null Or (tblProducts.ProductCode) <>[tblProducts].[ProductPrefix] &
Format([tblProducts].[ProductID],"00000")));
I'm having trouble with the FORMAT function.
Here is one method of doing the formatting:
UPDATE tblProducts
SET ProductCode = [ProductPrefix] +
RIGHT('00000' + CAST(COALESCE(ProductId, 0) as VARCHAR(255)), 5)
WHERE ProductCode Is Null OR
ProductCode <> [ProductPrefix] + RIGHT('00000' + CAST(ProductId as VARCHAR(255)), 5);
Actually, to prevent problems, I would phrase this as:
WITH toupdate AS (
SELECT p.*,
([ProductPrefix] +
RIGHT('00000' + CAST(COALESCE(ProductId, 0) as VARCHAR(255)), 5)
) as new_ProductCode
FROM tblProducts p
)
UPDATE toupdate
SET ProductCode = new_ProductCode
WHERE ProductCode Is Null OR
ProductCode <> new_ProductCode;
For SQL Server 2012 and up you can use:
UPDATE tblProducts
SET ProductCode = [ProductPrefix] + Format([ProductID],'00000')
WHERE ProductCode IS NULL OR ProductCode != [ProductPrefix] + Format([ProductID],'00000');
Another way with STUFF (SQL Server (starting with 2008)):
UPDATE tblProducts
SET ProductCode = [ProductPrefix] + STUFF('00000' + CAST(ProductID as nvarchar(10)),1,LEN(ProductID),'')
WHERE ProductCode IS NULL OR
ProductCode != [ProductPrefix] + STUFF('00000' + CAST(ProductID as nvarchar(10)),1,LEN(ProductID),'')
Use this instead of format([tblProducts].[ProductID], "00000")
right('00000'+cast([tblProducts].[ProductID] as varchar(5)),5)
