quitar - ¿Mejores técnicas para recortar ceros a la izquierda en SQL Server?

rellenar con ceros a la izquierda sql server 2012

Question

He estado usando this por un tiempo:

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

Sin embargo, recientemente encontré un problema con columnas con todos los caracteres "0" como "00000000" porque nunca encuentra un carácter que no sea "0" para que coincida.

Una técnica alternativa que he visto es usar TRIM :

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

Esto tiene un problema si hay espacios incrustados, ya que se convertirán en "0" cuando los espacios se vuelven a convertir en "0".

Estoy tratando de evitar un UDF escalar. He encontrado muchos problemas de rendimiento con UDF en SQL Server 2005.

Answer 1

¿Por qué no solo lanzas el valor a INTEGER y luego VARCHAR a VARCHAR ?

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

--------
       0

Answer 2

Aquí hay otras respuestas para no tomar en consideración si tiene cero (o incluso un solo cero).
Algunos siempre predeterminan una cadena vacía a cero, lo que es incorrecto cuando se supone que debe permanecer en blanco.
Vuelve a leer la pregunta original. Esto responde lo que quiere el Interlocutor.

Solución # 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"

Solución n. ° 2 (con datos de muestra):

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

Resultados:

Resumen:

Podrías usar lo que tengo arriba para una eliminación única de lead-zero''s.
Si planea reutilizarlo mucho, colóquelo en una función con valor de tabla en línea (ITVF).
Sus preocupaciones sobre los problemas de rendimiento con UDF son comprensibles.
Sin embargo, este problema solo se aplica a Funciones All-Scalar y Multi-Statement-Table-Functions.
Usar ITVF está perfectamente bien.

Tengo el mismo problema con nuestra base de datos de terceros.
Con los campos Alpha-Numeric, muchos entran sin los espacios principales, ¡dang humanos!
Esto hace que las uniones sean imposibles sin limpiar los ceros iniciales que faltan.

Conclusión:

En lugar de eliminar los ceros a la izquierda, es posible que desee considerar rellenar sus valores recortados con ceros a la izquierda cuando realice las uniones.
Mejor aún, limpie sus datos en la tabla agregando ceros a la izquierda y luego reconstruya sus índices.
Creo que esto sería mucho más rápido y menos complejo.

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

Answer 3

En lugar de un espacio, reemplace los 0 con un carácter de espacio en blanco "raro" que normalmente no debería estar en el texto de la columna. Un avance de línea probablemente sea suficiente para una columna como esta. Entonces puede LTrim normalmente y reemplazar el carácter especial con 0 de nuevo.

Answer 4

Esto hace una función agradable ...

DROP FUNCTION [dbo].[FN_StripLeading]
GO
CREATE FUNCTION [dbo].[FN_StripLeading] (@string VarChar(128), @stripChar VarChar(1))
RETURNS VarChar(128)
AS
BEGIN
-- http://.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

Answer 5

Lo siguiente devolverá ''0'' si la cadena consta completamente de ceros:

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

Answer 6

Mi versión de esto es una adaptación del trabajo de Arvo, con un poco más de agregado para garantizar otros dos casos.

1) Si tenemos todos los 0, deberíamos devolver el dígito 0.

2) Si tenemos un espacio en blanco, aún deberíamos devolver un carácter en blanco.

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

Answer 7

Prueba esto:

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

Answer 8

cast (value as int) siempre funcionará si string es un número

Answer 9

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

Esto tiene un límite en la longitud de la cadena que se puede convertir a un INT

Answer 10

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

Answer 11

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

La sugerencia de Thomas G funcionó para nuestras necesidades.

El campo en nuestro caso ya estaba en cadena y solo los ceros iniciales necesitaban ser recortados. En su mayoría, todo es numérico, pero a veces hay letras, por lo que la conversión INT anterior se bloqueará.