Formatar números preenchendo com zeros à esquerda no SQL Server

Temos uma tabela SQL antiga que foi usada pelo SQL Server 2000 por quase 10 anos.

Nele, os números de crachá dos nossos funcionários são armazenados como char(6) de 000001 a 999999 .

Estou escrevendo um aplicativo da web agora e preciso armazenar números de crachá de funcionário.

Na minha nova tabela, eu poderia pegar o atalho e copiar a tabela antiga, mas estou esperando por uma melhor transferência de dados, tamanho menor, etc, simplesmente armazenando os valores int de 1 a 999999 .

Em C #, posso formatar rapidamente um valor int para o número do emblema usando

 public static string GetBadgeString(int badgeNum) { return string.Format("{0:000000}", badgeNum); // alternate // return string.Format("{0:d6}", badgeNum); } 

Como eu modificaria essa consulta SQL simples para formatar o valor retornado também?

 SELECT EmployeeID FROM dbo.RequestItems WHERE ID=0 

Se EmployeeID for 7135, essa consulta deverá retornar 007135 .

Altere o número 6 para o tamanho total que você precisa:

 SELECT REPLICATE('0',6-LEN(EmployeeId)) + EmployeeId 

Se a coluna é um INT, você pode usar o RTRIM para convertê-lo implicitamente em um VARCHAR

 SELECT REPLICATE('0',6-LEN(RTRIM(EmployeeId))) + RTRIM(EmployeeId) 

E o código para remover esses 0s e recuperar o número ‘real’:

 SELECT RIGHT(EmployeeId,(LEN(EmployeeId) - PATINDEX('%[^0]%',EmployeeId)) + 1) 

Basta usar a function FORMAT (funciona no SQL Server 2012 ou mais recente):

 SELECT FORMAT(EmployeeID, '000000') FROM dbo.RequestItems WHERE ID=0 

Referência: http://msdn.microsoft.com/pt-br/library/hh213505.aspx

Você pode mudar seu procedimento desta maneira

 SELECT Right('000000' + CONVERT(NVARCHAR, EmployeeID), 6) AS EmpIDText, EmployeeID FROM dbo.RequestItems WHERE ID=0 

No entanto, isso pressupõe que o seu EmployeeID é um valor numérico e este código altera o resultado para uma string, sugiro adicionar novamente o valor numérico original

EDIT Claro que eu não li atentamente a pergunta acima. Ele diz que o campo é um char(6) portanto, EmployeeID não é um valor numérico. Embora essa resposta ainda tenha um valor em si, essa não é a resposta correta para a pergunta acima.

Odiava ter que CONVERTER o int, e isso parece muito mais simples. Pode até ter um desempenho melhor, pois há apenas uma conversão de string e uma adição simples.

selecione DIREITA (1000000 + EmployeeId, 6) …

Apenas certifique-se de que o “1000000” tenha pelo menos tantos zeros quanto o tamanho necessário.

Eu estou postando tudo em um lugar, tudo funciona para mim para preencher com 4 zero de liderança 🙂

 declare @number int = 1; print right('0000' + cast(@number as varchar(4)) , 4) print right('0000' + convert(varchar(4), @number) , 4) print right(replicate('0',4) + convert(varchar(4), @number) , 4) print cast(replace(str(@number,4),' ','0')as char(4)) print format(@number,'0000') 

Outra maneira, apenas por completude.

 DECLARE @empNumber INT = 7123 SELECT STUFF('000000', 6-LEN(@empNumber)+1, LEN(@empNumber), @empNumber) 

Ou, conforme sua consulta

 SELECT STUFF('000000', 6-LEN(EmployeeID)+1, LEN(EmployeeID), EmployeeID) AS EmployeeCode FROM dbo.RequestItems WHERE ID=0 

Tão limpo quanto poderia obter e dar espaço de substituição com variables:

 Select RIGHT(REPLICATE('0',6) + EmployeeID, 6) from dbo.RequestItems WHERE ID=0 

A partir da versão 2012 e em você pode usar

 SELECT FORMAT(EmployeeID,'000000') FROM dbo.RequestItems WHERE ID=0 
 SELECT replicate('0', 6 - len(employeeID)) + convert(varchar, employeeID) as employeeID FROM dbo.RequestItems WHERE ID=0 
 SELECT cast(replace(str(EmployeeID,6),' ','0')as char(6)) FROM dbo.RequestItems WHERE ID=0 

A solução funciona para números assinados / negativos com zeros à esquerda, para todas as versões do Sql:

 DECLARE @n money = -3, @length tinyint = 15, @decimals tinyint = 0 SELECT REPLICATE('-', CHARINDEX('-', @n, 1)) + REPLACE(REPLACE(str(@n, @length, @decimals), '-', ''), ' ', '0') 

Na minha versão do SQL, não consigo usar REPLICATE. Então eu fiz isso:

 SELECT CONCAT(REPEAT('0', 6-LENGTH(emplyeeID)), emplyeeID) AS emplyeeID FROM dbo.RequestItems`