Bunion Bunion - 3 months ago 7
SQL Question

Formating in SQL

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, can anyone point me in the right direction? Thanks

Answer

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),'')