Cast da varchar a decimal, con valori nulli
Marchi Giuseppe
Ho bisogno di fare un cast da una colonna di tipo varchar ad un tipo di data
decimal.
Il problema è che in quella colonna ho dei valori vuoti, quindi quando
eseguo la query
SELECT CONVERT(decimal, MIOCAMPO) AS expr1 FROM MIATABELLA
ricevo l'errore
Error converting data type varchar to numeric
questo anche se metto la clausola
WHERE MIOCAMPO <> ''
dove sbaglio ?
Luca Bianchi
> eseguo la query
>
> SELECT CONVERT(decimal, MIOCAMPO) AS expr1 FROM MIATABELLA
>
> ricevo l'errore
>
> Error converting data type varchar to numeric
Il problema dei campi con stringa vuota è facilmente risolvibile
trasformandoli in NULL; segui questo esempio
========================
CREATE TABLE dbo.T
(
c1 varchar(10)
)
GO
INSERT dbo.T VALUES ('1')
INSERT dbo.T VALUES ('2')
INSERT dbo.T VALUES ('')
INSERT dbo.T VALUES (NULL)
GO
SELECT CONVERT(decimal, NULLIF(c1, '')) AS expr1 FROM dbo.T
========================
Come vedi utilizzo la funzione NULLIF per convertire in NULL eventuali campi
in cui sia presente una stringa vuota...
> questo anche se metto la clausola
>
> WHERE MIOCAMPO <> ''
...anche in questo modo, se i "campi anomali" fossero quelli in cui sia
presente una stringa vuota, dovresti riuscire a soddisfare la query.
Evidentemente c'è qualche campo il cui contenuto non è compatibile con la
conversione e non si tratta di una stringa vuota. Esegui sulla tabella una
query del tipo
SELECT campi FROM tabella WHERE ISNUMERIC = 0
e vedrai che qualche cosa uscirà fuori...
Bye
--
Luca Bianchi
Microsoft MVP - SQL Server
http://community.ugiss.org/blogs/lbianchi
Marchi Giuseppe
Eseguendo la query
SELECT campo FROM tabella WHERE ISNUMERIC(campo) = 0
mi ritornano tutte le righe che hanno valore nullo o vuoto per il mio campo.
la query
SELECT CONVERT(decimal, NULLIF(campo, '')) AS expr1 FROM tabella
continua a darmi l'errore che ti ho riportato, nonappena vede che il valore
di quel campo è una stringa vuota.
quindi il mio problema non è sui valori nulli, ma sui valori vuoti...
anche facendo un replace in questo modo:
SELECT CONVERT(deciaml, REPLACE(campo, '', '0')) AS expr1 FROM tabella
mi ritorna sempre lo stesso errore.
--
"Luca Bianchi" <rightjoinR...@hotmail.com> wrote in message
news:CD65196B-2005-4D5C...@microsoft.com...
Luca Bianchi
> SELECT CONVERT(decimal, NULLIF(campo, '')) AS expr1 FROM tabella
>
> continua a darmi l'errore che ti ho riportato, nonappena vede che il
> valore di quel campo è una stringa vuota.
> quindi il mio problema non è sui valori nulli, ma sui valori vuoti...
Che il problema non era sui null era fuori discussione... e visto che
abbiamo manipolato le stringhe vuote (che virtualmente non esistono più)
significa che è altro a dare problemi...
> anche facendo un replace in questo modo:
>
> SELECT CONVERT(deciaml, REPLACE(campo, '', '0')) AS expr1 FROM tabella
>
> mi ritorna sempre lo stesso errore.
...adesso hai anche la prova provata che non sono i campi vuoti a darti
problemi, non trovi?
Cerca di capire quali sono i campi problematici (sicuramente non le stringhe
vuote che le hai già trattate).
Nella tabella che ti ho dato nel post precedente come esempio, prova ad
aggiungere la seguente riga
INSERT dbo.T VALUES (CHAR(1))
Questo inserisce un carattere che a vederlo ti sembrerà uno spazio, ma uno
spazio ha codice ASCII = 32; questo ha codice ASCII = 1. In questo caso la
"bonifica" con la funzione NULLIF non sortirebbe alcun effetto ed oltre alla
stringa vuota dovrai manipolare anche questa ulteriore anomalia
Marchi Giuseppe
ma come faccio per vedere che tipo di carattere c'è all'interno di quei
campi, che dalla management studio sembrano vuoti ?
e se volessi "bonificarli" in preparazione alla mia conversione ?
--
Marchi Giuseppe
MCP, MCTS WSS 3.0 - Application Development
www.peppedotnet.it
"Luca Bianchi" <rightjoinR...@hotmail.com> wrote in message
news:D8D5B37C-1FA0-4D70...@microsoft.com...
Luca Bianchi
> campi, che dalla management studio sembrano vuoti ?
Ad esempio utilizzando le funzioni LEN() per misurarne la lunghezza e
ASCII() per vedere, per ciascun carattere, qual'è l'effettivo codice ASCII
> e se volessi "bonificarli" in preparazione alla mia conversione ?
Ad esempio con la NULLIF... come nell'esempio che ti ho proposto per
trasformare le stringhe vuote in NULL... oppure escludendoli dalla query...
ma questo dipende da te...
Marcello
> ok. quindi niente campi nulli ne vuoti.
> e se volessi "bonificarli" in preparazione alla mia conversione ?
Ciao,
mi inserisco proponendo una query diversa che opera al contrario
cercando i valori validi, anzichč gli invalidi.
Proviamo a ragionare cosě, tra quei varchar potranno esserci:
- Valori vuoti o nulli
- Valori puramente numerici
- Valori con separatore dei decimali corretto
- Altre porcherie
usando l'operatore like potresti identificare i vari casi con where del
tipo:
- Valori vuoti o nulli
where nullif(campo,'') is null
- Valori puramente numerici:
where campo not like '%[^0-9]%'
- Valori con separatore dei decimali corretto
where campo like '%[0-9].[0-9]%'
and campo not like '%[^0-9]%.%'
and campo not like '%.%[^0-9]%'
- Atre porcherie
Not(where precedenti in or)
Prova a lavorarci un po', sono curioso di sapere dove stava l'inghippo.
marc.
--
Marcello Poletti
Dingo&Epomops
http://www.dingoepomops.it
http://blogs.dotnethell.it/epomops/
Marchi Giuseppe
SELECT ASCII(campo), campo FROM tabella
mi torna che i campi i che io vedo vuoti hanno valore ascii null
SELECT LEN(campo), campo FROM tabella
mi torna che i campi che io vedo vuoti hanno lunghezza zero
eppure tutte queste query fermano il casting nonappena incontrano un valore,
che a questo punto dovrebbe essere vuoto.
SELECT CONVERT(decimal, campo) FROM tabella
WHERE campo NOT LIKE '%[^0-9]%' OR NULLIF(campo,'') IS NULL
SELECT CONVERT(decimal, campo) FROM tabella
WHERE LEN(campo) <> 0
SELECT CONVERT(decimal, campo) FROM tabella
WHERE ASCII(campo) <> NULL
SELECT CONVERT(decimal, campo) FROM tabella
WHERE NULLIF(ASCII(campo), '')) IS NOT NULL
mi sembra di aver controllato tutti i casi possibili, ma mi ritrovo sempre
il medesimo errore di conversione quando arrivano i valori vuoti.
"Marcello" ha scritto:
> Marchi Giuseppe ha scritto:
> > ok. quindi niente campi nulli ne vuoti.
> > ma come faccio per vedere che tipo di carattere c'è all'interno di quei
> > campi, che dalla management studio sembrano vuoti ?
> > e se volessi "bonificarli" in preparazione alla mia conversione ?
>
> Ciao,
>
> mi inserisco proponendo una query diversa che opera al contrario
> cercando i valori validi, anzichè gli invalidi.
> Proviamo a ragionare così, tra quei varchar potranno esserci:
AlessandroD
news:F89956D0-E5C0-4548...@microsoft.com...
>
> eppure tutte queste query fermano il casting nonappena incontrano un
> valore,
> che a questo punto dovrebbe essere vuoto.
>
> SELECT CONVERT(decimal, campo) FROM tabella
> WHERE campo NOT LIKE '%[^0-9]%' OR NULLIF(campo,'') IS NULL
>
sopra.
Ciao, Alessandro
--
/*
Alessandro Dereani
Microsoft MVP - SQL Server
http://mvp.support.microsoft.com
*/
Marchi Giuseppe
Error converting data type varchar to numeric.
Mi accorgo che l'errore viene scatenato quando incontra un campo vuoto,
perchè per un attimo vedo i primi 4 risultati, che sono valorizzati, mentre
il quinto è vuoto.
"AlessandroD" ha scritto:
AlessandroD
> Msg 8114, Level 16, State 5, Line 1
> Error converting data type varchar to numeric.
>
> Mi accorgo che l'errore viene scatenato quando incontra un campo vuoto,
> perchè per un attimo vedo i primi 4 risultati, che sono valorizzati,
> mentre
> il quinto è vuoto.
>
Ovvio che ottieni quell'errore per i campi '', nella tua select hai gestito
la trasformazione di '' in null nella where, poi il convert lo applichi al
valore originario, che essendo '', produce l'errore che hai rilevato.
Quindi questo:
SELECT CONVERT(decimal, campo) FROM tabella
WHERE campo NOT LIKE '%[^0-9]%' OR NULLIF(campo,'') IS NULL
Deve diventare:
SELECT CONVERT(decimal, NULLIF(campo,'')) FROM tabella
WHERE campo NOT LIKE '%[^0-9]%' OR Campo= ''
Marchi Giuseppe
La query che mi hai proposto funziona perfettamente.
Poi l'ho riadattata, in base al contenuto del mio campo, così:
SELECT CONVERT(float, NULLIF(REPLACE(campo,',','.'),'0')) FROM tabella
WHERE campo NOT LIKE '%[^0-9]%' OR campo NOT LIKE '%[^0-9]%.%[^0-9]%'
Grazie ancora !
"AlessandroD" ha scritto:
AlessandroD
> Grande !
> La query che mi hai proposto funziona perfettamente.
>
> SELECT CONVERT(float, NULLIF(REPLACE(campo,',','.'),'0')) FROM tabella
> WHERE campo NOT LIKE '%[^0-9]%' OR campo NOT LIKE '%[^0-9]%.%[^0-9]%'
>
ti funziona sempre, buon per te... :-)
Perň mi par tanto strano visto che
OR campo NOT LIKE '%[^0-9]%.%[^0-9]%'
E' vera in moltissimi casi (tipo 'pippo'), quella condizione č falsa solo
per parti non numeriche separate dal . , tutto il resto passa
tranquillamente...
Se il tuo obiettivo č trasformare in numerico una stringa fatta da soli
numeri e con la virgola come separatore decimale credo che una where campo
not like '%[^0-9,]%' sia piů chiara e corretta.