Formatowanie liczb przez wypełnienie początkowymi zerami w programie SQL Server

Mamy starą tabelę SQL, która była używana przez SQL Server 2000 przez blisko 10 lat.

W nim przechowywane są numery naszych identyfikatorów pracowników char(6)od 000001do 999999.

Piszę teraz aplikację internetową i muszę przechowywać numery identyfikatorów pracowników.

W mojej nowej tabeli mógłbym skorzystać ze skrótu i ​​skopiować starą tabelę, ale mam nadzieję na lepszy transfer danych, mniejszy rozmiar itp., Po prostu przechowując intwartości od 1do 999999.

W C # mogę szybko sformatować intwartość numeru odznaki przy użyciu

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

Jak zmodyfikować to proste zapytanie SQL, aby również sformatować zwracaną wartość?

SELECT EmployeeID
FROM dbo.RequestItems
WHERE ID=0

Jeśli EmployeeIDwynosi 7135, to zapytanie powinno zwrócić 007135.

Zmień liczbę 6 na dowolną wymaganą całkowitą długość:

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

Jeśli kolumna to INT, możesz użyć RTRIM, aby niejawnie przekonwertować ją na VARCHAR

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

I kod do usunięcia tych zer i odzyskania „prawdziwej” liczby:

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

Po prostu użyj funkcji FORMAT (działa na SQL Server 2012 lub nowszym):

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

Źródła: http://msdn.microsoft.com/en-us/library/hh213505.aspx

W ten sposób możesz zmienić swoją procedurę

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

Jednak zakłada się, że twoja EmployeeIDjest wartością liczbową i ten kod zmienia wynik na ciąg, sugeruję ponowne dodanie oryginalnej wartości liczbowej

EDYCJA Oczywiście nie przeczytałem dokładnie powyższego pytania. Mówi, że pole jest char(6)więc EmployeeID nie jest wartością liczbową. Chociaż ta odpowiedź ma wartość samą w sobie, nie jest to poprawna odpowiedź na powyższe pytanie.

Nienawidziłem konieczności KONWERSJI int, a to wydaje się znacznie prostsze. Może nawet działać lepiej, ponieważ istnieje tylko jedna konwersja ciągów i proste dodawanie.

wybierz PRAWO (1000000 + EmployeeId, 6) ...

Tylko upewnij się, że wartość „1000000” ma co najmniej tyle zer, ile potrzeba.

Piszę wszystko w jednym miejscu, wszystko działa u mnie dopinanie z 4 wiodącymi zerami :)

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

Inny sposób, tylko dla kompletności.

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

Lub, zgodnie z zapytaniem

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

Od wersji 2012 i nowszych można używać

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

Tak czysty, jak to tylko możliwe i daje możliwość zamiany na zmienne:

Select RIGHT(REPLICATE('0',6) + EmployeeID, 6) 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

Rozwiązanie działa dla liczb ze znakiem / ujemnymi z wiodącymi zerami, dla wszystkich wersji Sql:

DECLARE
    @n money = -3,
    @length tinyint = 15,
    @decimals tinyint = 0

SELECT REPLICATE('-', CHARINDEX('-', @n, 1)) + REPLACE(REPLACE(str(@n, @length, @decimals), '-', ''), ' ', '0')

Najprostsze jest zawsze najlepsze:

Select EmployeeID*1 as EmployeeID 

W mojej wersji SQL nie mogę użyć REPLICATE. Więc zrobiłem to:

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