Greetings all, exercise in the book asks to write a query that "returns the existing numeric product ID, and the product ID formatted as a fixed-sized string with 10 digits with leading zeros. E.G. product 42 should be returned as '00000000042'
I've been trying to work this out by first casting productID (INT) to varchar, finding out how long the variable is, then adding the appropriate number of zeros. (ProductID is a max of 3 digits)
The book has a much shorter way of doing this, but I'm just a beginner.
QUESTION; why doesn't the = sign evaluate? I keep getting "incorrect syntax near '='. Also, if the = did work, am I allowed to use the "THEN-->CONCAT(.....)"?
DECLARE @pid VARCHAR(10)
SET @pid = CAST([productid] AS varchar(10))
WHEN LEN(@pid) = 1 THEN CONCAT(productid,'000000000')
WHEN LEN(@pid) = 2 THEN CONCAT(productid,'00000000')
WHEN LEN(@pid) = 3 THEN CONCAT(productid,'0000000')
END AS ProductionIDColumn
Thank you in advance!
P.S. how do you guys make your posts look so pretty (colors, scrolling, etc)?