Pad une chaîne avec des zéros en tête de sorte qu'il est de 3 caractères dans SQL Server 2008

Bien que la question était pour SQL Server 2008 R2, dans le cas où quelqu'un lit ceci avec la version 2012 et ci-dessus, depuis lors, il est devenu beaucoup plus facile par l'utilisation de FORMAT.

Vous pouvez passer soit une chaîne de format numérique standard ou personnalisé chaîne de format numérique, comme l'argument de format (merci Vadim Ovchinnikov pour cette astuce).

Pour cette question par exemple un code comme

DECLARE @myInt INT = 1;
-- One way using a standard numeric format string
PRINT FORMAT(@myInt,'D3');
-- Other way using a custom numeric format string
PRINT FORMAT(@myInt,'00#');

Sorties

001
001

La méthode sûre:

SELECT REPLACE(STR(n,3),' ','0')

Cela a L'avantage de renvoyer la chaîne '***' pour n 999, ce qui est un indicateur agréable et évident de l'entrée hors limites. Les autres méthodes listées ici échoueront silencieusement en tronquant l'entrée dans une sous-chaîne de 3 caractères.

Voici une technique plus générale pour le remplissage à gauche à n'importe quelle largeur désirée:

declare @x     int     = 123 -- value to be padded
declare @width int     = 25  -- desired width
declare @pad   char(1) = '0' -- pad character

select right_justified = replicate(
                           @pad ,
                           @width-len(convert(varchar(100),@x))
                           )
                       + convert(varchar(100),@x)

Cependant, si vous avez affaire à des valeurs négatives et que vous remplissez avec des zéros en tête, ni cette technique, ni aucune autre technique suggérée ne fonctionnera. Vous obtiendrez quelque chose qui ressemble à ceci:

00-123

[Probablement pas ce que vous vouliez]

Donc ... vous devrez sauter à travers quelques cerceaux supplémentaires Voici une approche qui formatera correctement les nombres négatifs:

declare @x     float   = -1.234
declare @width int     = 20
declare @pad   char(1) = '0'

select right_justified = stuff(
         convert(varchar(99),@x) ,                            -- source string (converted from numeric value)
         case when @x < 0 then 2 else 1 end ,                 -- insert position
         0 ,                                                  -- count of characters to remove from source string
         replicate(@pad,@width-len(convert(varchar(99),@x)) ) -- text to be inserted
         )

Il faut noter que le convert() appels doivent spécifier un [n]varchar de longueur suffisante pour contenir le résultat converti avec troncature.

Voici une variante de la réponse de Hogan que j'utilise dans SQL Server Express 2012:

SELECT RIGHT(CONCAT('000', field), 3)

Au lieu de s'inquiéter si le champ est une chaîne ou Non, Je l'ai juste CONCAT, car il va sortir une chaîne de toute façon. De plus, si le champ peut être un NULL, l'utilisation de ISNULL peut être nécessaire pour éviter que la fonction obtienne des résultats NULL.

SELECT RIGHT(CONCAT('000', ISNULL(field,'')), 3)

J'ai toujours trouvé la méthode suivante très utile.

REPLICATE('0', 5 - LEN(Job.Number)) + CAST(Job.Number AS varchar) as 'NumberFull'

Utilisez cette fonction qui convient à toutes les situations.

CREATE FUNCTION dbo.fnNumPadLeft (@input INT, @pad tinyint)
RETURNS VARCHAR(250)
AS BEGIN
    DECLARE @NumStr VARCHAR(250)

    SET @NumStr = LTRIM(@input)

    IF(@pad > LEN(@NumStr))
        SET @NumStr = REPLICATE('0', @Pad - LEN(@NumStr)) + @NumStr;

    RETURN @NumStr;
END

Exemple de sortie

SELECT [dbo].[fnNumPadLeft] (2016,10) -- returns 0000002016
SELECT [dbo].[fnNumPadLeft] (2016,5) -- returns 02016
SELECT [dbo].[fnNumPadLeft] (2016,2) -- returns 2016
SELECT [dbo].[fnNumPadLeft] (2016,0) -- returns 2016 

Pour ceux qui veulent mettre à jour leurs données existantes voici la requête:

update SomeEventTable set eventTime=RIGHT('00000'+ISNULL(eventTime, ''),5)

Pour les entiers, vous pouvez utiliser la conversion implicite de int en varchar:

SELECT RIGHT(1000 + field, 3)

A écrit ceci parce que j'avais des exigences pour une longueur spécifique (9). Pads la gauche avec le @ pattern seulement lorsque l'entrée a besoin de remplissage. Devrait toujours renvoyer la longueur définie dans @pattern.

declare @charInput as char(50) = 'input'

--always handle NULL :)
set @charInput = isnull(@charInput,'')

declare @actualLength as int = len(@charInput)

declare @pattern as char(50) = '123456789'
declare @prefLength as int = len(@pattern)

if @prefLength > @actualLength
    select Left(Left(@pattern, @prefLength-@actualLength) + @charInput, @prefLength)
else
    select @charInput

Renvoie 1234input

J'ai eu un problème similaire avec la colonne entière en entrée quand j'avais besoin d'une sortie varchar (ou string) de taille fixe. Par exemple, 1 '01', 12 "12". Ce code fonctionne:

SELECT RIGHT(CONCAT('00',field::text),2)

Si l'entrée est également une colonne de varchar, vous pouvez éviter la partie de coulée.

Je connais son ancien billet, j'ai juste pensé à le partager.

, j'ai trouvé ce code à la recherche d'une solution. Je ne sais pas si cela fonctionne sur toutes les versions de MSSQL j'ai MSSQL 2016.

declare @value as nvarchar(50) = 23
select REPLACE(STR(CAST(@value AS INT) + 1,4), SPACE(1), '0') as Leadingzero

Renvoie " 0023" Le 4 dans la fonction STR est la longueur totale, y compris la valeur. Les exemples 4, 23 et 123 auront tous 4 dans STR et la quantité correcte de zéros sera ajoutée. Vous pouvez augmenter ou de la diminuer. Pas besoin d'obtenir la longueur sur le 23.

Edit: je vois la même chose que @anon post.

C'est simple

Comme:

DECLARE @DUENO BIGINT
SET @DUENO=5

SELECT 'ND'+STUFF('000000',6-LEN(RTRIM(@DueNo))+1,LEN(RTRIM(@DueNo)),RTRIM(@DueNo)) DUENO