risposta-alla-domanda-sullo-sviluppo-web-bd.com

Tecniche migliori per tagliare gli zeri iniziali in SQL Server?

Sto usando questo per qualche tempo:

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

Tuttavia, di recente, ho riscontrato un problema con le colonne con tutti i caratteri "0" come "00000000" perché non trova mai un carattere diverso da "0".

Una tecnica alternativa che ho visto è usare TRIM:

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

Questo ha un problema se ci sono spazi incorporati, perché saranno trasformati in "0" quando gli spazi sono tornati in "0".

Sto cercando di evitare una UDF scalare. Ho riscontrato numerosi problemi di prestazioni con le UDF in SQL Server 2005.

125
Cade Roux
SUBSTRING(str_col, PATINDEX('%[^0]%', str_col+'.'), LEN(str_col))
221
Arvo

Perché non hai appena lanciato il valore su INTEGER e poi su VARCHAR?

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

--------
       0
26
Quassnoi

Altre risposte qui da non prendere in considerazione se si dispone di tutto-zero (o anche un singolo zero).
Alcuni impostano sempre una stringa vuota a zero, il che è errato quando si suppone che rimanga vuoto.
Rileggere la domanda originale. Questo risponde a ciò che l'interrogante vuole.

Soluzione 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"

Soluzione n. 2 (con dati di esempio):

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

Risultati:

MikeTeeVee_SQL_Server_Remove_Leading_Zeros

Sommario:

Potresti usare quello che ho sopra per una rimozione una tantum dei primi zero.
Se prevedi di riutilizzarlo molto, inseriscilo in una ITVF (Inline-Table-Valued-Function).
Le tue preoccupazioni relative ai problemi di prestazioni con le UDF sono comprensibili.
Tuttavia, questo problema si applica solo alle funzioni All-Scalar e Multi-Statement-Table.
L'utilizzo di ITVF è perfetto.

Ho lo stesso problema con il nostro database di terze parti.
Con i campi alfa-numerici molti sono entrati senza gli spazi iniziali, pericoli umani!
Ciò rende impossibile l'unione senza eliminare gli zeri iniziali mancanti.

Conclusione:

Invece di rimuovere gli zeri iniziali, si consiglia di prendere in considerazione solo il riempimento dei valori con gli zero iniziali quando si effettuano i join.
Ancora meglio, ripulisci i tuoi dati nella tabella aggiungendo zero iniziali, quindi ricostruendo gli indici.
Penso che questo sarebbe più veloce e meno complesso.

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

Invece di uno spazio, sostituisci gli 0 con un carattere di spazio bianco "raro" che normalmente non dovrebbe trovarsi nel testo della colonna. Un avanzamento di riga è probabilmente abbastanza buono per una colonna come questa. Quindi puoi LTrim normalmente e sostituire di nuovo il carattere speciale con 0.

5
Joel Coehoorn

Quanto segue restituirà '0' se la stringa consiste interamente di zeri:

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
3
Scott

Questo rende una bella funzione ....

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 = '%[^'[email protected]+']%'
    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
2
user2600313

cast (value as int) funzionerà sempre se string è un numero

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

Il suggerimento di Thomas G ha funzionato per i nostri bisogni. 

Il campo nel nostro caso era già una stringa e solo gli zeri iniziali dovevano essere tagliati. Per lo più è tutto numerico ma a volte ci sono lettere in modo che la precedente conversione INT si blocca. 

1
Randy

Se stai usando SQL Snowflake, potresti usare questo:

ltrim(str_col,'0')

La funzione ltrim rimuove tutte le istanze del set designato di caratteri dal lato sinistro. 

Quindi ltrim (str_col, '0') su '00000008A' restituire '8A'

E rtrim (str_col, '0.') Su '$ 125,00' restituire '$ 125'

0
JJFord3

La mia versione di questo è un adattamento del lavoro di Arvo, con un po 'di più aggiunto per garantire altri due casi.

1) Se abbiamo tutti 0, dovremmo restituire la cifra 0.

2) Se abbiamo uno spazio vuoto, dovremmo comunque restituire un carattere vuoto.

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
Brisbe
SELECT CAST(CAST('000000000' AS INTEGER) AS VARCHAR)

Questo ha un limite sulla lunghezza della stringa che può essere convertita in INT

0
Curt Ehrhart

Se non vuoi convertire in int, preferisco questa sotto la logica perché può gestire i null IFNULL (campo, LTRIM (campo, '0')) 

0
shockwave