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

J'ai une chaîne qui mesure jusqu'à 3 caractères lorsqu'elle est créée pour la première fois dans SQL Server 2008 R2.

Je voudrais le remplir avec des zéros en tête, donc si sa valeur d'origine était '1' alors la nouvelle valeur serait '001'. Ou si sa valeur d'origine était le '23', la nouvelle valeur est '023'. Ou si sa valeur d'origine est '124' alors la nouvelle valeur est la même que la valeur d'origine.

J'utilise SQL Server 2008 R2. Comment ferais-je cela en utilisant T-SQL?

271
demandé sur Luke Girvin 2013-05-26 19:47:13

13 réponses

Si le champ est déjà une chaîne, cela fonctionnera

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

Si vous voulez que les valeurs NULL s'affichent comme ' 000 '

Ce pourrait être un entier-alors vous voudriez

 SELECT RIGHT('000'+CAST(field AS VARCHAR(3)),3)

si la longueur

496
répondu Hogan 2018-09-13 14:08:49

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
86
répondu Géza 2018-06-22 13:58:52

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.

63
répondu Anon 2015-04-17 21:37:47

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.

26
répondu Nicholas Carey 2014-07-10 18:23:39

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)
19
répondu jahu 2015-03-27 16:46:50

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

REPLICATE('0', 5 - LEN(Job.Number)) + CAST(Job.Number AS varchar) as 'NumberFull'
14
répondu Bill 2014-12-10 15:11:39

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 
10
répondu Salar 2016-05-08 05:47:22

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

update SomeEventTable set eventTime=RIGHT('00000'+ISNULL(eventTime, ''),5)
4
répondu Alan B. Dee 2014-07-10 16:29:07

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

SELECT RIGHT(1000 + field, 3)
3
répondu Konstantin 2014-06-08 11:32:45

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

1
répondu nicky 2015-08-27 04:57:25

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.

1
répondu user3183867 2015-10-21 20:42:36

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.

1
répondu Niel Buys 2018-03-08 11:31:08

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
-1
répondu DEBASIS PAUL 2018-01-13 03:45:30