SQL Server에서 선행 0을 트리밍하기 위한 더 나은 기술

SQL Server에서 선행 0을 트리밍하기 위한 더 나은 기술

한동안 이걸 사용하고 있어요.

SUBSTRING(str_col, PATINDEX('%[^0]%', str_col), LEN(str_col))

그러나 최근에는 '00000000'과 같이 모든 '0'자가 포함된 열에 일치하는 '0'자가 없는 문제가 발견되었습니다.

제가 본 다른 방법은 이 기술을 사용하는 것입니다.TRIM:

REPLACE(LTRIM(REPLACE(str_col, '0', ' ')), ' ', '0')

내장된 공간이 있는 경우 공백이 다시 "0"으로 바뀌기 때문에 문제가 발생합니다.

스칼라 UDF를 회피하려고 합니다.SQL Server 2005에서는 UDF의 퍼포먼스 문제가 많이 발견되었습니다.

SUBSTRING(str_col, PATINDEX('%[^0]%', str_col+'.'), LEN(str_col))

그냥 가치 있는 일을 하는 게 어때?INTEGER그리고 나서VARCHAR?

SELECT  CAST(CAST('000000000' AS INTEGER) AS VARCHAR)

--------
       0

모두 0(또는 단일 0)인 경우에는 고려하지 않는 답변도 있습니다.
일부에서는 빈 문자열이 항상 0으로 기본 설정됩니다.빈 문자열이 공백인 채로 있어야 할 경우에는 잘못된 것입니다.
원래의 질문을 다시 읽습니다.질문자가 원하는 게 뭔지 알 수 있습니다

솔루션 1:

--This example uses both Leading and Trailing zero's.
--Avoid losing those Trailing zero's and converting embedded spaces into more zeros.
--I added a non-whitespace character ("_") to retain trailing zero's after calling Replace().
--Simply remove the RTrim() function call if you want to preserve trailing spaces.
--If you treat zero's and empty-strings as the same thing for your application,
--  then you may skip the Case-Statement entirely and just use CN.CleanNumber .
DECLARE @WackadooNumber VarChar(50) = ' 0 0123ABC D0 '--'000'--
SELECT WN.WackadooNumber, CN.CleanNumber,
       (CASE WHEN WN.WackadooNumber LIKE '%0%' AND CN.CleanNumber = '' THEN '0' ELSE CN.CleanNumber END)[AllowZero]
 FROM (SELECT @WackadooNumber[WackadooNumber]) AS WN
 OUTER APPLY (SELECT RTRIM(RIGHT(WN.WackadooNumber, LEN(LTRIM(REPLACE(WN.WackadooNumber + '_', '0', ' '))) - 1))[CleanNumber]) AS CN
--Result: "123ABC D0"

솔루션 #2(샘플 데이터 사용):

SELECT O.Type, O.Value, Parsed.Value[WrongValue],
       (CASE WHEN CHARINDEX('0', T.Value)  > 0--If there's at least one zero.
              AND LEN(Parsed.Value) = 0--And the trimmed length is zero.
             THEN '0' ELSE Parsed.Value END)[FinalValue],
       (CASE WHEN CHARINDEX('0', T.Value)  > 0--If there's at least one zero.
              AND LEN(Parsed.TrimmedValue) = 0--And the trimmed length is zero.
             THEN '0' ELSE LTRIM(RTRIM(Parsed.TrimmedValue)) END)[FinalTrimmedValue]
  FROM 
  (
    VALUES ('Null', NULL), ('EmptyString', ''),
           ('Zero', '0'), ('Zero', '0000'), ('Zero', '000.000'),
           ('Spaces', '    0   A B C '), ('Number', '000123'),
           ('AlphaNum', '000ABC123'), ('NoZero', 'NoZerosHere')
  ) AS O(Type, Value)--O is for Original.
  CROSS APPLY
  ( --This Step is Optional.  Use if you also want to remove leading spaces.
    SELECT LTRIM(RTRIM(O.Value))[Value]
  ) AS T--T is for Trimmed.
  CROSS APPLY
  ( --From @CadeRoux's Post.
    SELECT SUBSTRING(O.Value, PATINDEX('%[^0]%', O.Value + '.'), LEN(O.Value))[Value],
           SUBSTRING(T.Value, PATINDEX('%[^0]%', T.Value + '.'), LEN(T.Value))[TrimmedValue]
  ) AS Parsed

결과:

요약:.

위의 내용을 사용하여 선행 0을 한 번만 제거할 수 있습니다.
많이 재사용할 경우 Inline-Table-Valued-Function(ITVF; 인라인 테이블 값 함수)에 배치합니다.
UDF의 퍼포먼스 문제에 대한 우려는 이해할 수 있습니다.
단, 이 문제는 All-Scalar-Functions 및 Multi-Statement-Table-Functions에만 적용됩니다.
ITVF를 사용하는 것은 문제 없습니다.

서드파티 데이터베이스에 대해서도 같은 문제가 있습니다.
영숫자 필드에서는 선두 공간 없이 많은 항목이 입력됩니다.
이것에 의해, 없어진 선두 제로를 청소하지 않으면, 조인 할 수 없게 됩니다.

결론:

조인할 때 선행 제로를 삭제하는 대신 트리밍된 값을 선행 제로로 채우는 것을 고려해 보십시오.
또한 선행 0을 추가한 다음 인덱스를 재구축하여 테이블의 데이터를 정리하는 것이 좋습니다.
나는 이것이 훨씬 빠르고 덜 복잡할 것이라고 생각한다.

SELECT RIGHT('0000000000' + LTRIM(RTRIM(NULLIF(' 0A10  ', ''))), 10)--0000000A10
SELECT RIGHT('0000000000' + LTRIM(RTRIM(NULLIF('', ''))), 10)--NULL --When Blank.

공백 대신 0을 보통 열의 텍스트에 없어야 하는 '희귀' 공백 문자로 대체합니다.이런 컬럼에는 줄바꿈으로도 충분할 겁니다.그런 다음 일반 LTrim을 사용하여 특수 문자를 다시 0으로 바꿀 수 있습니다.

나의 버전은 Arvo의 작품을 개작한 것이며, 다른 두 가지 경우를 확실히 하기 위해 조금 더 추가되었다.

1) 0이 모두 있는 경우 숫자 0을 반환해야 합니다.

2) 공백이 있는 경우에도 공백 문자를 반환해야 합니다.

CASE 
    WHEN PATINDEX('%[^0]%', str_col + '.') > LEN(str_col) THEN RIGHT(str_col, 1) 
    ELSE SUBSTRING(str_col, PATINDEX('%[^0]%', str_col + '.'), LEN(str_col))
 END

문자열이 모두 0으로 구성되면 다음에서 '0'을 반환합니다.

CASE WHEN SUBSTRING(str_col, PATINDEX('%[^0]%', str_col+'.'), LEN(str_col)) = '' THEN '0' ELSE SUBSTRING(str_col, PATINDEX('%[^0]%', str_col+'.'), LEN(str_col)) END AS str_col

이것은 훌륭한 기능이 됩니다.

DROP FUNCTION [dbo].[FN_StripLeading]
GO
CREATE FUNCTION [dbo].[FN_StripLeading] (@string VarChar(128), @stripChar VarChar(1))
RETURNS VarChar(128)
AS
BEGIN
-- http://stackoverflow.com/questions/662383/better-techniques-for-trimming-leading-zeros-in-sql-server
    DECLARE @retVal VarChar(128),
            @pattern varChar(10)
    SELECT @pattern = '%[^'+@stripChar+']%'
    SELECT @retVal = CASE WHEN SUBSTRING(@string, PATINDEX(@pattern, @string+'.'), LEN(@string)) = '' THEN @stripChar ELSE SUBSTRING(@string, PATINDEX(@pattern, @string+'.'), LEN(@string)) END
    RETURN (@retVal)
END
GO
GRANT EXECUTE ON [dbo].[FN_StripLeading] TO PUBLIC

문자열이 숫자일 경우 cast(int 값)는 항상 작동합니다.

SELECT CAST(CAST('000000000' AS INTEGER) AS VARCHAR)

이것은 INT로 변환할 수 있는 문자열의 길이에 제한이 있습니다.

Snowflake SQL을 사용하는 경우 다음을 사용할 수 있습니다.

ltrim(str_col,'0')

ltrim 함수는 지정된 문자 집합의 모든 인스턴스를 왼쪽에서 제거합니다.

00000008의 ltrim(str_col, '0')은A'는 '8A'를 반환합니다.

$125.00의 rtrim(str_col, '0.')은 $125를 반환합니다.

이것이 도움이 될 수 있다

SELECT ABS(column_name) FROM [db].[schema].[table]

replace(ltrim(replace(Fieldname.TableName, '0', '')), '', '0')

Thomas G의 제안은 우리의 요구를 충족시켰다.

이 경우 필드는 이미 문자열이었고 선행 0만 잘라야 했습니다.대부분 숫자이지만 문자가 있는 경우가 있기 때문에 이전 INT 변환이 크래시됩니다.

  SUBSTRING(str_col, IIF(LEN(str_col) > 0, PATINDEX('%[^0]%', LEFT(str_col, LEN(str_col) - 1) + '.'), 0), LEN(str_col))

'0', '00' 등에서도 정상적으로 동작합니다.

번호를 varchar로 int로 변환하려면 simple을 사용할 수도 있습니다.

(column + 0)

숫자 값만 사용하는 경우 매우 쉬운 방법입니다.

SELECT
    TRY_CONVERT(INT, '000053830')

이것을 시험해 보세요.

replace(ltrim(replace(@str, '0', ' ')), ' ', '0')

int로 변환하고 싶지 않은 경우 null IFNULL(필드)을 처리할 수 있기 때문에 아래의 논리를 선호합니다.LTRIM(필드, '0')

SQL Server 2022(16.x)부터는 이 기능을 사용할 수 있습니다.

TRIM ( [ LEADING | TRAILING | BOTH ] [characters FROM ] string )

MySQL에서 이 작업을 수행할 수 있습니다.

Trim(Leading '0' from your_column)

언급URL : https://stackoverflow.com/questions/662383/better-techniques-for-trimming-leading-zeros-in-sql-server