CODEWITHSUNDEEP
sqlsql-serverms-access
Bunion
Bunion

Reputation: 461

Formatting 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.

Upvotes: 3

Views: 106

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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;

Upvotes: 5

gofr1
gofr1

Reputation: 15977

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

Upvotes: 2

Serg
Serg

Reputation: 22811

Use this instead of format([tblProducts].[ProductID], "00000")

right('00000'+cast([tblProducts].[ProductID] as varchar(5)),5)

Upvotes: 2

Related Questions