왼쪽의 varchar를 특정 길이로 패딩하는 가장 효율적인 T-SQL 방법?
다음과 같은 말을 합니다.
REPLICATE(@padchar, @len - LEN(@str)) + @str
이는 SQL을 어떻게 사용하든 비효율적으로 사용하는 것입니다.
아마 같은 것
right('XXXXXXXXXXXX'+ rtrim(@str), @n)
여기서 X는 패딩 문자, @n은 결과 문자열의 문자 수입니다(고정길이로 인해 패딩이 필요한 경우).
하지만 이미 말씀드렸듯이 데이터베이스에서는 이 작업을 피해야 합니다.
이것은 2008년에 처음 요청되었지만 SQL Server 2012에서 도입된 몇 가지 새로운 기능이 있습니다.FORMAT 함수는 0으로 남겨진 패딩을 매우 단순화합니다.또, 다음의 변환도 실시합니다.
declare @n as int = 2
select FORMAT(@n, 'd10') as padWithZeros
업데이트:
FORMAT 기능의 실제 효율을 직접 테스트해 보고 싶었습니다.Alex Cuse의 원래 답변에 비해 효율이 그다지 좋지 않다는 것을 알고 매우 놀랐습니다.FORMAT 기능은 깔끔하지만 실행 시간 면에서는 그다지 효율적이지 않습니다.제가 사용한 Talley 테이블에는 64,000개의 레코드가 있습니다.마틴 스미스에게 실행 시간의 효율성을 지적한 것에 대해 찬사를 보냅니다.
SET STATISTICS TIME ON
select FORMAT(N, 'd10') as padWithZeros from Tally
SET STATISTICS TIME OFF
SQL Server 실행 시간: CPU 시간 = 2157 ms, 경과 시간 = 2696 ms.
SET STATISTICS TIME ON
select right('0000000000'+ rtrim(cast(N as varchar(5))), 10) from Tally
SET STATISTICS TIME OFF
SQL Server 실행 시간:
CPU 시간 = 31 ms, 경과 시간 = 235 ms.
여러 사람이 이에 대한 버전을 제공했습니다.
right('XXXXXXXXXXXX'+ @str, @n)
n보다 길면 실제 데이터가 잘리기 때문에 주의해야 합니다.
@padstr = REPLICATE(@padchar, @len) -- this can be cached, done only once
SELECT RIGHT(@padstr + @str, @len)
어쩌면 과잉 살상일지도 몰라 이 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)
이게 도움이 됐으면 좋겠어요.
STUFF ( character_expression , start , length ,character_expression )
select stuff(@str, 1, 0, replicate('0', @n - len(@str)))
아마도 과잉 살상일 겁니다. 저는 종종 이 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)
select right(replicate(@padchar, @len) + @str, @len)
SQL Server 2005 이후에서는 이를 위해 CLR 함수를 생성할 수 있습니다.
이거 어때:
replace((space(3 - len(MyField))
은 3의 입니다.zeros를 채우다
이걸로 할게요.결과 길이를 결정할 수 있으며 기본 패딩 문자가 제공되지 않은 경우 기본 패딩 문자를 지정할 수 있습니다.물론 입력과 출력의 길이를 최대치에 관계없이 커스터마이즈할 수 있습니다.
/*===============================================================
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
웰포인트 메디케이드 사업부
다음은 잘린 문자열을 피하고 일반 ol' SQL을 사용하는 솔루션입니다.@AlexCuse, @Kevin 및 @Sklivz는 이 코드의 기반이 되는 솔루션을 제공하고 있습니다.
--[@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 변수(및 현재 존재하는 제한) 외에 2개의 스위치 간의 위치가 표시되어 있습니다.두 상태 모두에서 동일한 실행 시간에 동일한 결과를 얻은 기능을 사용하여 프로시저를 실행했습니다.SQLServer2016에서는 다른 제품과 달리 효율성에 차이가 없습니다.
어쨌든, 이것은 제가 몇 년 전에 작성한 UDF와 오늘의 변경 사항입니다.왼쪽/오른쪽 파라미터 옵션과 에러 체크 기능이 있는 것 이외에는 다른 것과 거의 동일합니다.
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에 10진수 x가 붙어 있는 기능은 CREATE FUNTION [dbo]입니다.[LPAD_DEC] ( -- @pad nvarchar(MAX), @string nvarchar(MAX), @length int, @dec int ) 함수의 매개 변수를 추가합니다. nvarchar(max) AS BEGIN -- 여기서 반환 변수를 선언합니다.
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이 반환됩니다.
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
소수점 두 자리까지 반올림하고 필요한 경우 0으로 오른쪽 패드를 채우는 수치를 제공하려면 다음과 같이 하십시오.
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 형식으로 이메일로 보내고 있으므로 데이터를 해석하는 추가 도구를 사용하지 않고 정보를 포맷하고 싶습니다.
여기 보통 바차 패딩하는 방법이 있습니다.
WHILE Len(@String) < 8
BEGIN
SELECT @String = '0' + @String
END
언급URL : https://stackoverflow.com/questions/121864/most-efficient-t-sql-way-to-pad-a-varchar-on-the-left-to-a-certain-length
'programing' 카테고리의 다른 글
| 'COLLATE SQL_Latin1_General_CP1'이란CI_AS는? (0) | 2023.04.08 |
|---|---|
| 쿼리에 의해 반환되는 각 행에 대해 저장 프로시저를 한 번 실행하려면 어떻게 해야 합니까? (0) | 2023.04.08 |
| SQL Server에서 레코드를 삭제한 후 ID 시드 재설정 (0) | 2023.04.08 |
| SQL Server에서 ID 열을 업데이트하는 방법 (0) | 2023.04.08 |
| EPPlus의 자동 열 폭 (0) | 2023.04.08 |