Самый эффективный способ T-SQL дополнить varchar слева до определенной длины?

Это просто неэффективное использование SQL, как бы вы это ни делали.

возможно что-то вроде

right('XXXXXXXXXXXX'+ rtrim(@str), @n)

где X - это ваш символ заполнения, а @n - количество символов в результирующей строке (при условии, что вам нужно заполнение, потому что вы имеете дело с фиксированной длиной).

Но, как я уже сказал, вам действительно следует избегать этого в своей базе данных.

Я знаю, что изначально об этом спрашивали еще в 2008 году, но в SQL Server 2012 появилось несколько новых функций. Функция FORMAT красиво упрощает заполнение нулями слева. Он также выполнит преобразование за вас:

declare @n as int = 2
select FORMAT(@n, 'd10') as padWithZeros

Обновление:

Я хотел сам проверить реальную эффективность функции ФОРМАТ. Я был очень удивлен, обнаружив, что эффективность была не очень хорошей по сравнению с исходным ответом от AlexCuse. Хотя я считаю, что функция очистки FORMAT не очень эффективна с точки зрения времени выполнения. Таблица Tally, которую я использовал, содержит 64 000 записей. Престижность Мартина Смита за указание на эффективность времени выполнения.

SET STATISTICS TIME ON
select FORMAT(N, 'd10') as padWithZeros from Tally
SET STATISTICS TIME OFF

Время выполнения SQL Server: время ЦП = 2157 мс, затраченное время = 2696 мс.

SET STATISTICS TIME ON
select right('0000000000'+ rtrim(cast(N as varchar(5))), 10) from Tally
SET STATISTICS TIME OFF

Время выполнения SQL Server:

Процессорное время = 31 мс, затраченное время = 235 мс.

Несколько человек дали версии этого:

right('XXXXXXXXXXXX'+ @str, @n)

будьте осторожны с этим, потому что он усечет ваши фактические данные, если они длиннее n.

Возможно, у меня есть эти UDF для прокладки влево и вправо

ALTER   Function [dbo].[fsPadLeft](@var varchar(200),@padChar char(1)='0',@len int)
returns varchar(300)
as
Begin

return replicate(@PadChar,@len-Len(@var))+@var

end

и вправо

ALTER function [dbo].[fsPadRight](@var varchar(200),@padchar char(1)='0', @len int) returns varchar(201) as
Begin

--select @padChar=' ',@len=200,@var='hello'


return  @var+replicate(@PadChar,@len-Len(@var))
end

Я не уверен, что метод, который вы даете, действительно неэффективен, но альтернативный способ, если он не должен быть гибким по длине или символу заполнения, был бы (при условии, что вы хотите дополнить его " От 0 "до 10 символов:

DECLARE
   @pad_characters VARCHAR(10)

SET @pad_characters = '0000000000'

SELECT RIGHT(@pad_characters + @str, 10)

наверное перебор, я часто использую этот UDF:

CREATE FUNCTION [dbo].[f_pad_before](@string VARCHAR(255), @desired_length INTEGER, @pad_character CHAR(1))
RETURNS VARCHAR(255) AS  
BEGIN

-- Prefix the required number of spaces to bulk up the string and then replace the spaces with the desired character
 RETURN ltrim(rtrim(
        CASE
          WHEN LEN(@string) < @desired_length
            THEN REPLACE(SPACE(@desired_length - LEN(@string)), ' ', @pad_character) + @string
          ELSE @string
        END
        ))
END

Чтобы вы могли делать что-то вроде:

select dbo.f_pad_before('aaa', 10, '_')

Мне понравилось решение vnRocks, вот оно в виде udf

create function PadLeft(
      @String varchar(8000)
     ,@NumChars int
     ,@PadChar char(1) = ' ')
returns varchar(8000)
as
begin
    return stuff(@String, 1, 0, replicate(@PadChar, @NumChars - len(@String)))
end

это простой способ добавить левый край:

REPLACE(STR(FACT_HEAD.FACT_NO, x, 0), ' ', y)

Где x - номер контактной площадки, а y - символ контактной площадки.

образец:

REPLACE(STR(FACT_HEAD.FACT_NO, 3, 0), ' ', 0)

В SQL Server 2005 и более поздних версиях вы можете создать для этого функцию CLR.

Как насчет этого:

replace((space(3 - len(MyField))

3 - это число zeros для заполнения

Я надеюсь, что это помогает кому-то.

STUFF ( character_expression , start , length ,character_expression )

select stuff(@str, 1, 0, replicate('0', @n - len(@str)))

Я пользуюсь этим. Это позволяет вам определить длину, которую вы хотите получить, а также символ заполнения по умолчанию, если он не указан. Конечно, вы можете настроить длину ввода и вывода для любых максимумов, с которыми вы работаете.

/*===============================================================
 Author         : Joey Morgan
 Create date    : November 1, 2012
 Description    : Pads the string @MyStr with the character in 
                : @PadChar so all results have the same length
 ================================================================*/
 CREATE FUNCTION [dbo].[svfn_AMS_PAD_STRING]
        (
         @MyStr VARCHAR(25),
         @LENGTH INT,
         @PadChar CHAR(1) = NULL
        )
RETURNS VARCHAR(25)
 AS 
      BEGIN
        SET @PadChar = ISNULL(@PadChar, '0');
        DECLARE @Result VARCHAR(25);
        SELECT
            @Result = RIGHT(SUBSTRING(REPLICATE('0', @LENGTH), 1,
                                      (@LENGTH + 1) - LEN(RTRIM(@MyStr)))
                            + RTRIM(@MyStr), @LENGTH)

        RETURN @Result

      END

Ваш пробег может отличаться. :-)

Джои Морган
Главный программист / аналитик I
Подразделение WellPoint Medicaid

Вот мое решение, которое избегает усеченных строк и использует простой старый SQL. Спасибо @AlexCuse, @Kevin и @Sklivvz, чьи решения являются основой этого кода.

 --[@charToPadStringWith] is the character you want to pad the string with.
declare @charToPadStringWith char(1) = 'X';

-- Generate a table of values to test with.
declare @stringValues table (RowId int IDENTITY(1,1) NOT NULL PRIMARY KEY, StringValue varchar(max) NULL);
insert into @stringValues (StringValue) values (null), (''), ('_'), ('A'), ('ABCDE'), ('1234567890');

-- Generate a table to store testing results in.
declare @testingResults table (RowId int IDENTITY(1,1) NOT NULL PRIMARY KEY, StringValue varchar(max) NULL, PaddedStringValue varchar(max) NULL);

-- Get the length of the longest string, then pad all strings based on that length.
declare @maxLengthOfPaddedString int = (select MAX(LEN(StringValue)) from @stringValues);
declare @longestStringValue varchar(max) = (select top(1) StringValue from @stringValues where LEN(StringValue) = @maxLengthOfPaddedString);
select [@longestStringValue]=@longestStringValue, [@maxLengthOfPaddedString]=@maxLengthOfPaddedString;

-- Loop through each of the test string values, apply padding to it, and store the results in [@testingResults].
while (1=1)
begin
    declare
        @stringValueRowId int,
        @stringValue varchar(max);

    -- Get the next row in the [@stringLengths] table.
    select top(1) @stringValueRowId = RowId, @stringValue = StringValue
    from @stringValues 
    where RowId > isnull(@stringValueRowId, 0) 
    order by RowId;

    if (@@ROWCOUNT = 0) 
        break;

    -- Here is where the padding magic happens.
    declare @paddedStringValue varchar(max) = RIGHT(REPLICATE(@charToPadStringWith, @maxLengthOfPaddedString) + @stringValue, @maxLengthOfPaddedString);

    -- Added to the list of results.
    insert into @testingResults (StringValue, PaddedStringValue) values (@stringValue, @paddedStringValue);
end

-- Get all of the testing results.
select * from @testingResults;

Я знаю, что на данный момент это мало что добавляет к разговору, но я запускаю процедуру генерации файла, и она идет невероятно медленно. Я использовал реплики, видел этот метод обрезки и решил, что попробую.

В моем коде вы можете видеть, что переключение между ними происходит в дополнение к новой переменной @padding (и существующему ограничению). Я запустил свою процедуру с функцией в обоих состояниях с одинаковыми результатами по времени выполнения. Так что, по крайней мере, в SQLServer2016 я не вижу никакой разницы в эффективности, которую обнаружили другие.

В любом случае, вот мой UDF, который я написал много лет назад, плюс изменения сегодня, которые во многом такие же, как и у других, за исключением того, что в нем есть параметр LEFT / RIGHT и некоторая проверка ошибок.

CREATE FUNCTION PadStringTrim 
(
    @inputStr varchar(500), 
    @finalLength int, 
    @padChar varchar (1),
    @padSide varchar(1)
)
RETURNS VARCHAR(500)

AS BEGIN
    -- the point of this function is to avoid using replicate which is extremely slow in SQL Server
    -- to get away from this though we now have a limitation of how much padding we can add, so I've settled on a hundred character pad 
    DECLARE @padding VARCHAR (100) = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
    SET @padding = REPLACE(@padding, 'X', @padChar)


    SET @inputStr = RTRIM(LTRIM(@inputStr))

    IF LEN(@inputStr) > @finalLength 
        RETURN '!ERROR!' -- can search for ! in the returned text 

    ELSE IF(@finalLength > LEN(@inputStr))
        IF @padSide = 'L'
            SET @inputStr = RIGHT(@padding + @inputStr, @finalLength)
            --SET @inputStr = REPLICATE(@padChar, @finalLength - LEN(@inputStr)) + @inputStr
        ELSE IF @padSide = 'R'
            SET @inputStr = LEFT(@inputStr + @padding, @finalLength)
            --SET @inputStr = @inputStr + REPLICATE(@padChar, @finalLength - LEN(@inputStr)) 



    -- if LEN(@inputStr) = @finalLength we just return it 
    RETURN @inputStr;
END

-- SELECT  dbo.PadStringTrim( tblAccounts.account, 20, '~' , 'R' ) from tblAccounts
-- SELECT  dbo.PadStringTrim( tblAccounts.account, 20, '~' , 'L' ) from tblAccounts

У меня есть одна функция lpad с десятичными знаками x: CREATE FUNCTION [dbo]. [LPAD_DEC] (- Добавьте сюда параметры функции @pad nvarchar (MAX), @string nvarchar (MAX), @length int, @dec int ) RETURNS nvarchar (max) AS BEGIN - Объявите здесь возвращаемую переменную DECLARE @resp nvarchar (max)

IF LEN(@string)=@length
BEGIN
    IF CHARINDEX('.',@string)>0
    BEGIN
        SELECT @resp = CASE SIGN(@string)
            WHEN -1 THEN
                -- Nros negativos grandes con decimales
                concat('-',SUBSTRING(replicate(@pad,@length),1,@length-len(@string)),ltrim(str(abs(@string),@length,@dec)))
            ELSE
                -- Nros positivos grandes con decimales
                concat(SUBSTRING(replicate(@pad,@length),1,@length-len(@string)),ltrim(str(@string,@length,@dec)))                  
            END
    END
    ELSE
    BEGIN
        SELECT @resp = CASE SIGN(@string)
            WHEN -1 THEN
                --Nros negativo grande sin decimales
                concat('-',SUBSTRING(replicate(@pad,@length),1,(@length-3)-len(@string)),ltrim(str(abs(@string),@length,@dec)))
            ELSE
                -- Nros positivos grandes con decimales
                concat(SUBSTRING(replicate(@pad,@length),1,@length-len(@string)),ltrim(str(@string,@length,@dec)))                  
            END                     
    END
END
ELSE
    IF CHARINDEX('.',@string)>0
    BEGIN
        SELECT @resp =CASE SIGN(@string)
            WHEN -1 THEN
                -- Nros negativos con decimales
                concat('-',SUBSTRING(replicate(@pad,@length),1,@length-len(@string)),ltrim(str(abs(@string),@length,@dec)))
            ELSE
                --Ntos positivos con decimales
                concat(SUBSTRING(replicate(@pad,@length),1,@length-len(@string)),ltrim(str(abs(@string),@length,@dec))) 
            END
    END
    ELSE
    BEGIN
        SELECT @resp = CASE SIGN(@string)
            WHEN -1 THEN
                -- Nros Negativos sin decimales
                concat('-',SUBSTRING(replicate(@pad,@length-3),1,(@length-3)-len(@string)),ltrim(str(abs(@string),@length,@dec)))
            ELSE
                -- Nros Positivos sin decimales
                concat(SUBSTRING(replicate(@pad,@length),1,(@length-3)-len(@string)),ltrim(str(abs(@string),@length,@dec)))
            END
    END
RETURN @resp

КОНЕЦ

Вот мое решение. Я могу набить любого персонажа, и это быстро. Пошла с простотой. Вы можете изменить переменный размер в соответствии с вашими потребностями.

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

CREATE OR ALTER FUNCTION code.fnConvert_PadLeft(
    @in_str nvarchar(1024),
    @pad_length int, 
    @pad_char nchar(1) = ' ', 
    @rtn_null NVARCHAR(1024) = '')
RETURNS NVARCHAR(1024)
AS
BEGIN
     DECLARE @rtn  NCHAR(1024) = ' '
     RETURN RIGHT(REPLACE(@rtn,' ',@pad_char)+ISNULL(@in_str,@rtn_null), @pad_length)
END
GO

CREATE OR ALTER FUNCTION code.fnConvert_PadRight(
    @in_str nvarchar(1024), 
    @pad_length int, 
    @pad_char nchar(1) = ' ', 
    @rtn_null NVARCHAR(1024) = '')
RETURNS NVARCHAR(1024)
AS
BEGIN
     DECLARE @rtn  NCHAR(1024) = ' '
     RETURN LEFT(ISNULL(@in_str,@rtn_null)+REPLACE(@rtn,' ',@pad_char), @pad_length)
END
GO 

-- Example
SET STATISTICS time ON 
SELECT code.fnConvert_PadLeft('88',10,'0',''), 
    code.fnConvert_PadLeft(null,10,'0',''), 
    code.fnConvert_PadLeft(null,10,'0',null), 
    code.fnConvert_PadRight('88',10,'0',''), 
    code.fnConvert_PadRight(null,10,'0',''),
    code.fnConvert_PadRight(null,10,'0',NULL)


0000000088  0000000000  NULL    8800000000  0000000000  NULL

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

DECLARE @value = 20.1
SET @value = ROUND(@value,2) * 100
PRINT LEFT(CAST(@value AS VARCHAR(20)), LEN(@value)-2) + '.' + RIGHT(CAST(@value AS VARCHAR(20)),2)

Если кто-то может придумать более аккуратный способ, это будет оценено - приведенное выше кажется неуклюжим.

Примечание: в этом случае я использую SQL Server для отправки отчетов по электронной почте в формате HTML и поэтому хочу отформатировать информацию без использования дополнительных инструментов для анализа данных.

Вот как я обычно добавляю varchar

WHILE Len(@String) < 8
BEGIN
    SELECT @String = '0' + @String
END