Форматирование чисел путем заполнения ведущими нулями в SQL Server

у нас есть старая таблица SQL, которая использовалась SQL Server 2000 в течение почти 10 лет.

в нем наши номера значков сотрудников хранятся как char(6) С 000001 до 999999.

сейчас я пишу веб-приложение, и мне нужно хранить номера значков сотрудников.

в моей новой таблице я мог бы взять короткий путь и скопировать старую таблицу, но я надеюсь на лучшую передачу данных, меньший размер и т. д., Просто сохранив int значения 1 to 999999.

в C#, я могу быстро отформатировать int значение для номера значка с помощью

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

как бы я изменил этот простой SQL-запрос, чтобы отформатировать возвращаемое значение?

SELECT EmployeeID
FROM dbo.RequestItems
WHERE ID=0

если EmployeeID это 7135, этот запрос должен вернуть 007135.

12 91
sql sql-server tsql string-formatting

12 ответов:

измените число 6 на то, что ваша общая длина должна быть:

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

Если столбец является INT, вы можете использовать RTRIM для неявного преобразования его в VARCHAR 

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

и код, чтобы удалить эти 0s и получить обратно "реальный" номер:

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

просто используйте функцию форматирования (работает на SQL Server 2012 или новее):

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

Ссылка:http://msdn.microsoft.com/en-us/library/hh213505.aspx

80

вы можете изменить свою процедуру таким образом

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

однако это предполагает, что ваш EmployeeID - это числовое значение, и этот код изменяет результат на строку, я предлагаю снова добавить исходное числовое значение

EDIT конечно, я не читал внимательно вопрос выше. Он говорит, что поле это char(6) поэтому EmployeeID не является числовым значением. Хотя этот ответ по-прежнему имеет значение как таковое, он не является правильным ответом на вопрос выше.

28

ненавидел необходимость конвертировать int, и это кажется намного проще. Может даже работать лучше, так как есть только одно преобразование строк и простое добавление.

выберите справа (1000000 + EmployeeId, 6) ...

просто убедитесь, что" 1000000 " имеет по крайней мере столько нулей, сколько требуется размер.

21

Я размещаю все в одном месте, все работает для меня, чтобы положить с 4 ведущих нуля :)

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

другой способ, просто для полноты.

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

или, согласно вашему запросу

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

как чистый, как он мог бы получить и дать область замены с переменными:

Select RIGHT(REPLICATE('0',6) + EmployeeID, 6) from dbo.RequestItems
WHERE ID=0
4

С версии 2012 и дальше вы можете использовать

SELECT FORMAT(EmployeeID,'000000')
FROM dbo.RequestItems
WHERE ID=0
4
SELECT replicate('0', 6 - len(employeeID)) + convert(varchar, employeeID) as employeeID
FROM dbo.RequestItems 
WHERE ID=0
3
SELECT 
    cast(replace(str(EmployeeID,6),' ','0')as char(6)) 
FROM dbo.RequestItems
WHERE ID=0
2

решение работает для подписанных / отрицательных чисел с ведущими нулями, для всех версий Sql:

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

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

в моей версии SQL я не могу использовать REPLICATE. Так я и сделал:

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