Home / Questions / 563424
Accepted
Hectorepg
Asked: 2022-10-21 13:54:13 +0800 CST 2022-10-21 13:54:13 +0800 CST

How to insert a certain number of digits in SQL?

  • 772

I am trying to insert a record with 3 digits, example: if it is the first one that comes out 001 and the consecutive one is 002, when reaching 10! let 010 come out.

in my code what I achieve is that I insert it without the two zeros

enter image description here

declare @Resultado int
SELECT @Resultado = SUBSTRING(MAX(Agente), CHARINDEX('V', MAX(Agente)) + 1, 4) + 1
FROM Agente
WHERE Agente LIKE '%204%'
AND Tipo = 'Agente'

print @Resultado

    insert into Agente
        (Agente, Nombre, Estatus, Conciliar, Nomina, Logico1, Logico2, Equipo)
    values
        ('204V'  + Convert(varchar, @Resultado), 'Lalo Garcia', 'ALTA', 1, 1, 0, 0, 0)
end

I hope you can support me.

sql

1 Answers

  1. Best Answer

    It is a simple solution.

    You have a numeric variable, as a receiver of the number that comes from a query. Then if you leave only the 3 characters on the right to a string type variable filled with 3 zeros and concatenating what the numeric has, you already have the leading zeros.

    Declare @resultado int = 1;
Declare @cadena varchar(6);
Set @cadena = Concat('000',Cast(@resultado as varchar(3)));
Set @cadena = RIGHT(@cadena,3);

Select @cadena;

    Res1

    in your code.

    declare @Resultado int;
Declare @cadena varchar(6);

SELECT @Resultado = SUBSTRING(MAX(Agente), CHARINDEX('V', MAX(Agente)) + 1, 4) + 1
FROM Agente
WHERE Agente LIKE '%204%'
AND Tipo = 'Agente'

print @Resultado

Set @cadena = Concat('000',Cast(@resultado as varchar(3)));
Set @cadena = RIGHT(@cadena,3);

    insert into Agente
        (Agente, Nombre, Estatus, Conciliar, Nomina, Logico1, Logico2, Equipo)
    values
        ((Concat('204V',@cadena)) , 'Lalo Garcia', 'ALTA', 1, 1, 0, 0, 0);

    Format invoice numbers

    Note : It is only supposed to hold 3 digits, and so the string variable is created with a varchar(6). If it can hold more, then you will have to size it larger and control the characters to be picked by the right function.

    • 0