Voer een tekenreeks in met voorloopnullen zodat deze 3 tekens lang is in SQL Server 2008

Ik heb een tekenreeks die maximaal 3 tekens lang is wanneer deze voor het eerst wordt gemaakt in SQL Server 2008 R2.

Ik zou het willen vullen met voorloopnullen, dus als de oorspronkelijke waarde ‘1’ was, zou de nieuwe waarde ‘001’ zijn. Of als de oorspronkelijke waarde ’23’ was, is de nieuwe waarde ‘023’. Of als de oorspronkelijke waarde ‘124’ is, is de nieuwe waarde hetzelfde als de oorspronkelijke waarde.

Ik gebruik SQL Server 2008 R2. Hoe zou ik dit doen met T-SQL?

Antwoord 1, autoriteit 100%

Als het veld al een string is, zal dit werken

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

Als u nulls wilt weergeven als ‘000’

Het kan een geheel getal zijn — dan zou je willen

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

Zoals de vraag vereist, werkt dit antwoord alleen als de lengte <= 3, als je iets groters wilt, moet je de stringconstante en de twee integerconstanten veranderen in de benodigde breedte. bijv. '0000' and VARCHAR(4)),4

Antwoord 2, autoriteit 22%

Hoewel de vraag voor SQL Server 2008 R2 was, mocht iemand dit lezen met versie 2012 en hoger, sindsdien is het veel gemakkelijker geworden door het gebruik van FORMAAT.

Je kunt een standaard tekenreeks met numerieke notatieof een aangepaste numerieke notatietekenreeksals het formaatargument (bedankt Vadim Ovchinnikovvoor deze hint).

Voor deze vraag bijvoorbeeld een code als

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#');

uitgangen

001
001

Antwoord 3, autoriteit 19%

De veilige methode:

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

Dit heeft het voordeel dat de string '***'wordt geretourneerd voor n < 0 of n > 999, wat een mooie en voor de hand liggende indicator is van out-of-bounds invoer. De andere methoden die hier worden vermeld, zullen stilletjes mislukken door de invoer af te kappen tot een subtekenreeks van 3 tekens.

Antwoord 4, autoriteit 5%

Hier is een variant van Hogans antwoord die ik gebruik in SQL Server Express 2012:

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

In plaats van me zorgen te maken of het veld een string is of niet, CONCAThet gewoon, omdat het toch een string zal produceren. Bovendien, als het veld een NULLkan zijn, kan het gebruik van ISNULLvereist zijn om te voorkomen dat de functie NULLresultaten krijgt.

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

Antwoord 5, Autoriteit 4%

Hier is een meer algemene techniek voor de linkervulling op elke gewenste breedte:

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)

Als u echter te maken hebt met negatieve waarden, en opvulling met toonaangevende nullen, zullen noch dit, noch andere voorgestelde techniek werken. Je krijgt iets dat er zo uitziet:

00-123

[Waarschijnlijk niet wat u wilde]

Dus … je zult door een aantal extra hoepels moeten springen, hier is een aanpak die op de juiste manier negatieve nummers zal formatteren:

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
         )

Men moet opmerken dat de convert()oproepen moet opgeven een [n]varcharvan voldoende lengte om het geconverteerde resultaat met truncatie te houden.

Antwoord 6, Autoriteit 3%

Ik heb altijd de volgende methode gevonden om zeer nuttig te zijn.

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

Antwoord 7, Autoriteit 2%

Gebruik deze functie die bij elke situatie past.

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

Voorbeelduitvoer

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 

Antwoord 8

Voor degenen die hun bestaande gegevens hier willen bijwerken, is de query:

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

Antwoord 9

Voor gehele getallen kunt u impliciete conversie van int naar varchar gebruiken:

SELECT RIGHT(1000 + field, 3)

Antwoord 10

Probeer dit met vaste lengte.

select right('000000'+'123',5)
select REPLICATE('0', 5 - LEN(123)) + '123'

Antwoord 11

Ik weet dat dit een oud ticket is, maar ik wilde dit even delen:

Ik heb deze code gevonden die een oplossing biedt. Ik weet niet zeker of het op alle versies van MSSQL werkt; Ik heb MSSQL 2016.

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

Dit levert “0023” op.

De 4 in de STR-functie is de totale lengte, inclusief de waarde. 4, 23 en 123 hebben bijvoorbeeld allemaal 4 in STR en het juiste aantal nullen wordt toegevoegd. Je kunt het verhogen of verlagen. Het is niet nodig om de lengte op de 23 te krijgen.

Bewerken: ik zie dat het hetzelfde is als het bericht van @Anon.

Antwoord 12

Ik had een soortgelijk probleem met een integer-kolom als invoer toen ik varchar (of string) uitvoer met een vaste grootte nodig had. Bijvoorbeeld 1 tot ’01’, 12 tot ’12’. Deze code werkt:

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

Als de invoer ook een varchar-kolom is, kunt u het castinggedeelte vermijden.

Antwoord 13

Probeer dit voor een meer dynamische aanpak.

declare @val varchar(5)
declare @maxSpaces int
set @maxSpaces = 3
set @val = '3'
select concat(REPLICATE('0',@maxSpaces-len(@val)),@val)

Antwoord 14

Ik schreef dit omdat ik eisen had voor een bepaalde lengte (9).
Vult de linkerzijde ALLEEN op met @pattern wanneer de invoer opvulling nodig heeft.
Moet altijd de lengte retourneren die is gedefinieerd in @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

Retourneert 1234invoer

Antwoord 15

Eenvoudig is dat

Vind ik leuk:

DECLARE @DUENO BIGINT
SET @DUENO=5
SELECT 'ND'+STUFF('000000',6-LEN(RTRIM(@DueNo))+1,LEN(RTRIM(@DueNo)),RTRIM(@DueNo)) DUENO

Antwoord 16

Ik kwam hier specifiek om uit te zoeken hoe ik mijn timezoneoffset kon converteren naar een timezone string voor het converteren van datums naar DATETIMEOFFSET in SQL Server 2008. Bruto, maar noodzakelijk.

Dus ik heb 1 methode nodig die kan omgaan met negatieve en positieve getallen, en deze indien nodig opmaakt tot twee tekens met een voorloopnul. Anons antwoordbracht me dichtbij, maar negatieve tijdzonewaarden kwamen uit als 0-5in plaats van de vereiste -05

Dus met een kleine aanpassing aan zijn antwoord werkt dit voor alle tijdzone-uurconversies

DECLARE @n INT = 13 -- Works with -13, -5, 0, 5, etc
SELECT CASE 
    WHEN @n < 0 THEN '-' + REPLACE(STR(@n * -1 ,2),' ','0') 
    ELSE '+' + REPLACE(STR(@n,2),' ','0') END + ':00'

Antwoord 17

Ik hoop dat dit nuttig is, maar het kan off-topic zijn. Ik was op zoek naar een oplossing om een prefix-voorbeeld “PQC-0” en voorloopnul tegelijkertijd toe te voegen aan mijn auto-increment-ID.

SELECT LPAD((SELECT CONCAT(‘000’,(CAST(field AS CHAR))) ),10,”PQC-0″) ALS ID UIT tabel

of

SELECT CONCAT(“000″,(SELECT LPAD((CAST(id AS CHAR)),6,”0”))) UIT tabel

Antwoord 18

Ik heb deze functie gemaakt die geschikt is voor bigint en één voorloopnul of een ander enkel teken (max. 20 tekens geretourneerd) en waarmee de lengte van de resultaten kleiner is dan de lengte van het invoernummer:

create FUNCTION fnPadNum (
  @Num BIGINT --Number to be padded, @sLen BIGINT --Total length of results , @PadChar varchar(1))
  RETURNS VARCHAR(20)
  AS
  --Pads bigint with leading 0's
            --Sample:  "select dbo.fnPadNum(201,5,'0')" returns "00201"
            --Sample:  "select dbo.fnPadNum(201,5,'*')" returns "**201"
            --Sample:  "select dbo.fnPadNum(201,5,' ')" returns "  201"
   BEGIN
     DECLARE @Results VARCHAR(20)
     SELECT @Results = CASE 
     WHEN @sLen >= len(ISNULL(@Num, 0))
     THEN replicate(@PadChar, @sLen - len(@Num)) + CAST(ISNULL(@Num, 0) AS VARCHAR)
     ELSE CAST(ISNULL(@Num, 0) AS VARCHAR)
     END
     RETURN @Results
     END
     GO
     --Usage:
      SELECT dbo.fnPadNum(201, 5,'0')
      SELECT dbo.fnPadNum(201, 5,'*')
      SELECT dbo.fnPadNum(201, 5,' ')