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

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

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

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

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

В С# я могу быстро форматировать значение 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.

Ответ 1

Измените номер 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)

Ответ 2

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

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

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

Ответ 3

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

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

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

ИЗМЕНИТЬ Конечно, я не внимательно прочитал вопрос выше. В нем указано, что поле char(6), поэтому EmployeeID не является числовым значением. Хотя этот ответ по-прежнему имеет значение как таковое, это не правильный ответ на вопрос выше.

Ответ 4

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

выберите ПРАВО (1000000 + EmployeeId, 6)...

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

Ответ 5

Я размещаю все в одном месте, все работает для меня, чтобы проложить 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')

Ответ 6

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

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

Ответ 7

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

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

Ответ 8

Начиная с версии 2012 и вы можете использовать

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

Ответ 9

SELECT replicate('0', 6 - len(employeeID)) + convert(varchar, employeeID) as employeeID
FROM dbo.RequestItems 
WHERE ID=0

Ответ 10

SELECT 
    cast(replace(str(EmployeeID,6),' ','0')as char(6)) 
FROM dbo.RequestItems
WHERE ID=0

Ответ 11

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

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

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

Ответ 12

Самое простое всегда самое лучшее:

Select EmployeeID*1 as EmployeeID

Ответ 13

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

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