Wypełnij ciąg wiodącymi zerami, aby miał 3 znaki w SQL Server 2008

Mam ciąg, który ma do 3 znaków, gdy jest tworzony po raz pierwszy w SQL Server 2008 R2.

Chciałbym uzupełnić go wiodącymi zerami, więc jeśli jego oryginalna wartość to „1”, wówczas nowa wartość to „001”. Lub jeśli jego pierwotna wartość wynosiła „23”, nową wartością jest „023”. Lub jeśli jego pierwotna wartość to „124”, wówczas nowa wartość jest taka sama jak wartość oryginalna.

Używam SQL Server 2008 R2. Jak miałbym to zrobić za pomocą T-SQL?

Jeśli pole jest już ciągiem, zadziała

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

Jeśli chcesz, aby wartości null były wyświetlane jako „000”

Może to być liczba całkowita - wtedy byś chciał

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

Jak wymaga tego pytanie, ta odpowiedź działa tylko wtedy, gdy długość <= 3, jeśli chcesz czegoś większego, musisz zmienić stałą łańcucha i dwie stałe liczb całkowitych na wymaganą szerokość. na przykład'0000' and VARCHAR(4)),4

Chociaż pytanie dotyczyło SQL Server 2008 R2, na wypadek, gdyby ktoś czyta to w wersji 2012 i nowszych, od tego czasu stało się to znacznie łatwiejsze dzięki zastosowaniu FORMATU .

Jako argument formatu możesz przekazać standardowy ciąg formatu liczbowego lub niestandardowy ciąg formatu liczbowego (dziękuję Vadimowi Ovchinnikovowi za tę wskazówkę).

Dla tego pytania na przykład taki kod

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

wyjścia

001
001

Bezpieczna metoda:

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

Ma to tę zaletę, że zwraca ciąg '***'dla n <0 lub n> 999, co jest ładnym i oczywistym wskaźnikiem danych wejściowych poza zakresem. Inne metody wymienione tutaj zawiodą po cichu przez obcięcie danych wejściowych do 3-znakowego podłańcucha.

Oto bardziej ogólna technika wypełniania lewej strony do dowolnej pożądanej szerokości:

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)

Jeśli jednak masz do czynienia z wartościami ujemnymi i dopełnianiem zer wiodących, ani ta, ani inna sugerowana technika nie będzie działać. Otrzymasz coś, co wygląda następująco:

00-123

[Prawdopodobnie nie to, co chciałeś]

Więc… będziesz musiał przeskoczyć kilka dodatkowych obręczy Oto jedno podejście, które odpowiednio sformatuje liczby ujemne:

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
         )

Należy zauważyć, że convert()wywołania powinny określać [n]varchardługość wystarczającą do przechwycenia przekonwertowanego wyniku z obcięciem.

Oto wariant odpowiedzi Hogana, której używam w SQL Server Express 2012:

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

Zamiast martwić się, czy pole jest łańcuchem, czy nie, po prostu CONCATgo, ponieważ i tak wyświetli łańcuch. Dodatkowo, jeśli pole może być a NULL, użycie ISNULLmoże być konieczne, aby uniknąć uzyskiwania NULLwyników przez funkcję .

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

Zawsze uważałem, że następująca metoda jest bardzo pomocna.

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

Użyj tej funkcji, która pasuje do każdej sytuacji.

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

Próbka wyjściowa

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 

Dla tych, którzy chcą zaktualizować swoje istniejące dane, oto zapytanie:

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

W przypadku liczb całkowitych możesz użyć niejawnej konwersji z int na varchar:

SELECT RIGHT(1000 + field, 3)

Znam jego stary bilet, po prostu chciałem go udostępnić.

Znalazłem ten kod szukający rozwiązania. Nie jestem pewien, czy to działa na wszystkich wersjach MSSQL Mam MSSQL 2016.

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

zwraca „0023” 4 w funkcji STR to całkowita długość łącznie z wartością. Przykłady 4, 23 i 123 będą miały 4 w STR i zostanie dodana poprawna liczba zer. Możesz go zwiększyć lub zmniejszyć. Nie musisz uzyskiwać długości na 23.

Edycja: Widzę, że jest taki sam jak post @Anon.

Miałem podobny problem z kolumną całkowitą jak wejście, gdy potrzebowałem wyjścia varchar (lub łańcucha) o stałej wielkości. Na przykład od 1 do „01”, od 12 do „12”. Ten kod działa:

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

Jeśli wejście jest również kolumną varchar, możesz uniknąć części odlewniczej.

Aby uzyskać bardziej dynamiczne podejście, wypróbuj to.

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

Wypróbuj to ze stałą długością.

select right('000000'+'123',5)

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

Napisałem to, ponieważ miałem wymagania do określonej długości (9). Wypełnia lewą stronę @pattern TYLKO wtedy, gdy wejście wymaga dopełnienia. Zawsze powinien zwracać długość zdefiniowaną w @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

Zwraca 1234 wejścia

To proste

Lubić:

DECLARE @DUENO BIGINT
SET @DUENO=5

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

Przybyłem tutaj, aby dowiedzieć się, jak przekonwertować przesunięcie strefy czasowej na ciąg strefy czasowej do konwertowania dat na DATETIMEOFFSET w SQL Server 2008. Brutto, ale konieczne.

Potrzebuję więc 1 metody, która poradzi sobie z liczbami ujemnymi i dodatnimi, formatując je na dwa znaki z wiodącym zerem w razie potrzeby. Odpowiedź Anonsa zbliżyła mnie, ale pojawiłyby się ujemne wartości w strefie czasowej0-5 wymagane-05

Więc z drobną poprawką w odpowiedzi, działa to dla wszystkich konwersji godzinnych w strefie czasowej

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'

Stworzyłem tę funkcję, która obsługuje bigint i jeden wiodący zero lub inny pojedynczy znak (zwrócono maksymalnie 20 znaków) i pozwala na uzyskanie długości wyników mniejszej niż długość liczby wejściowej:

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,' ')