Change length for field with '0' use Formula in-DB

Question

I want to adjust the length of a string field and fill '0' in left in Formula in-DB, and get an error message as below image.

I have limited knowledge on SQL in-DB formula, so I think there is some wrong in my SQL expression. Hope I can get some hints in Community.

Thank you so much.

TessaXu · Answer

Hi, it works when I use the statement add CONVERT() .

Great thanks for your quick reply, have a nice day.

DataNath · Answer

Hey @TessaXu, could you try the following?

RIGHT(REPLICATE('0',12) + "SALES_DOCUMENT",12)

If you have any further issues, I'd double check the datatype of the incoming [Sales Document] field. If it's numeric you could just CONVERT() within the function using an expression along these lines:

RIGHT(REPLICATE('0',12) + CONVERT(VARCHAR,"Product Line"),12)