How to add 0 in a column?

Question

I am beginner in SQL language and would like a help to run a query.

I need a new column with the data: code+size+color (I concatenated and it worked)

the problem I can’t solve is that the color needs to have 3 characters, but it has colors that have a character in id and colors that have 2 characters.

I need that when there is 1 character in the code to add 2 zeros.

and when there are 2 characters in the code add 1 more zero.

example:

cod.    cod_novo

1       001
2       002
3       003
99      099


    SELECT
        "Estoque_Atual"."Ordem_Prod_Serv",
        "Estoque_Atual"."Ordem_Tamanho",
        "Estoque_Atual"."Ordem_Cor",
        "Prod_Serv"."Codigo",
        "Prod_Serv"."Nome",
        "Cores"."Nome" AS "nome_cor",
        "Tamanhos"."Nome" AS "tamanho",
        "Tamanhos"."Codigo",
        "Cores"."Codigo",
        "Estoque_Atual"."Qtde_Estoque_Atual",
        CONCAT ( "prod_serv"."codigo", "tamanhos"."codigo", '0', "Cores"."codigo"  ) AS GRADE 
    FROM
        "Estoque_Atual"
        INNER JOIN "Prod_Serv" ON "Prod_Serv"."Ordem" = "Estoque_Atual"."Ordem_Prod_Serv"
        LEFT OUTER JOIN "Tamanhos" ON "Tamanhos"."Ordem" = "Estoque_Atual"."Ordem_Tamanho"
        LEFT OUTER JOIN "Cores" ON "Cores"."Ordem" = "Estoque_Atual"."Ordem_Cor" 
    WHERE
        "Prod_Serv"."Codigo" = '1009'
        AND "Estoque_atual"."Qtde_Estoque_Atual" > 0
    GROUP BY
        "Estoque_Atual"."Ordem",
        "Estoque_Atual"."Ordem_Prod_Serv",
        "Estoque_Atual"."Ordem_Tamanho",
        "Estoque_Atual"."Ordem_Cor",
        "Estoque_Atual"."Qtde_Estoque_Atual",
        "Prod_Serv"."Codigo",
        "Prod_Serv"."Nome",
        "Cores"."Nome",
        "Tamanhos"."Nome",
        "Tamanhos"."Codigo",
           "Cores"."Codigo" 

Answer 1

It is possible to make a replicate of '0' depending on the size (len) of the value present in the field:

concat(replicate('0', 3 - len(campo)),campo) as meu_novo_campo

---

With your field, it would look like this:

concat(replicate('0', 3 - len("Cores"."codigo")),"Cores"."codigo")

Joining with the other fields:

CONCAT ( "prod_serv"."codigo", "tamanhos"."codigo", concat(replicate('0', 3 - len("Cores"."codigo")),"Cores"."codigo") ) AS GRADE

---

See an online example: http://sqlfiddle.com/#! 18/ffa2f/7