SQL formatting field

Question

I have the following structure:

|VALOR|
|  1  |
|  23 |
|  5  |
|  41 |

I have a need to transform:

| VALOR |
|  0001 |
|  0023 |
|  0005 |
|  0041 |

Is there any function I can do that?

Answer 1

If the field is char:

Select REPLICATE('0', 4 - LEN('23')) + RTrim('23')

Answer 2

You can use the FORMAT function.

SELECT FORMAT(valor, '000000') FROM tabela;

References:

• Mysql: https://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_format
• SQL Server: https://docs.microsoft.com/pt-br/sql/t-sql/functions/format-transact-sql?view=sql-server-2017