Konvertierung varchar to float

[Robin_]

Hallo Zusammen,

ich hänge seit einiger Zeit an einer Aufgabe. Möchte über eine SQL-Abfrage ein Textfeld in Datentyp float umzuwandeln. In einem Datenbankfeld sind Werte hinterlegt, z.B.

Tabelle "Test"

Zeit                Wert
2011-06-21         9,997765E-03
2011-06-21         11,797439
2011-06-21         42393,59765
2011-06-21         1339-7891845
2011-06-21         30-10

Nun ist mein Ziel, dass alle Werte nach dieser Aktualisierungsabfrage einheitlich erfasst sind. Hierfür müsste ich in Worten folgende Abfragen durchführen:

1. Ersetze alle ',' und '-' durch ein '.'

Dies versuche ich duch den folgenden Code:

CREATE VIEW varchar2float
AS
SELECT CONVERT(float,REPLACE(REPLACE(Wert,',','.'),'-','.')) AS Wert
FROM Test

GO


Das View lässt sich zwar erstellen, aber wenn man die Daten lesen will, tritt der Fehler "Error converting data type varchar to float" auf.

Hat jemand eine Idee ?

Danke im Voraus

Grüße

Robin

[ebis]

Hierfür müsste ich in Worten folgende Abfragen durchführen:

1. Ersetze alle ',' und '-' durch ein '.'

Warum??? "-" ist doch Bestandteil der Zahl...
und bei der ersten String hast Du dann zwei Punkte im String...
In was soll das denn konvertiert werden???

CREATE VIEW varchar2float
AS
SELECT CONVERT(float,REPLACE(REPLACE(Wert,',','.'),'-','.')) AS Wert
FROM Test

Das View lässt sich zwar erstellen, aber wenn man die Daten lesen will, tritt der Fehler "Error converting data type varchar to float" auf.

Hat jemand eine Idee ?

siehe oben
[HIGHLIGHT="SQL"]
WITH Test (WERT)
AS ( SELECT '9,997765E-03'
UNION ALL SELECT '11,797439'
UNION ALL SELECT '42393,59765'
UNION ALL SELECT '1339,7891845'
UNION ALL SELECT '30E-10'
)
SELECT CONVERT(FLOAT (53),REPLACE(Wert,',','.')) AS Wert
FROM Test[/HIGHLIGHT]

[HIGHLIGHT="Result"]Wert
----------------------
0,009997765
11,797439
42393,59765
1339,7891845
3E-09[/HIGHLIGHT]

und zu FLOAT (N) siehe MS-Hilfe

[Robin_]

Hallo ebis,

danke für die Antwort. Dein Code löst jedoch die Aufgabe nicht.
Ich erkläre es auch warum.

Wenn in dem Datenbankfeld von Typ varchar nur Zahlen hinterlegt sind, z.B.

Tabelle "Test"

Wert
9,997765E-03
11,797439
42393,59765
1339,7891845
30E-10

funktioniert dein Code

    WITH Test (WERT)
       AS ( SELECT '9,997765E-03'
            UNION ALL SELECT '11,797439'
            UNION ALL SELECT '42393,59765'
            UNION ALL SELECT '1339,7891845'
            UNION ALL SELECT '30E-10'
          )  
    SELECT CONVERT(FLOAT (53),REPLACE(Wert,',','.')) AS Wert
    FROM Test

auch einwandfei.

ABER, wenn in dem Datenbankfeld sowohl Zahlen als auch Strings hinterlegt sind, z.B.

Wert
9,997765E-03
11,797439
10-40
40-90
42393,59765
1339,7891845
30-10

dann funktioniert der Code nicht mehr.

Nun ist das Ziel, dass man alle Einträge in der Spalte "Wert" (d.h. Zahlen und Strings) einheitlich in Typ float umwandelt.

d.h. ich möchte einerseits 9,997765E-03 in float und andererseits 10-40 in float konvertieren.

Hierfür müsste ich die folgenden Abfragen durchführen:
1. Ersetze bei Werten der Form 9,997765E-03 das Komma mit Punkt und konvertiere in float
2. Ersetze bei Strings der Form 10-40 das Minuszeichen mit Punkt und konvertiere in float.

Das versuche ich durch den Code:

CREATE VIEW varchar2float
AS
SELECT CONVERT(float,REPLACE(REPLACE(Wert,',','.'),'-','.')) AS Wert
FROM Test

GO

ist aber nicht richtig, weil beim ersten String zwei Punkte im String stehen.

Habt Ihr eine andere Idee ?

Danke im Voraus,
Grüße

Robin

[ebis]

Hierfür müsste ich die folgenden Abfragen durchführen:
1. Ersetze bei Werten der Form 9,997765E-03 das Komma mit Punkt und konvertiere in float
2. Ersetze bei Strings der Form 10-40 das Minuszeichen mit Punkt und konvertiere in float.

dafür gibt es CASE
und in TSQL gibt es die Funktion ISNUMERIC

[Robin_]

Hallo ebis,

danke für den Hinweis. Nun habe ich folgende Funktion gebaut:

CREATE FUNCTION varchar2float
(@werte as varchar(50))
RETURNS float
AS
BEGIN
	DECLARE @Return float

			if ISNUMERIC(@werte) = 1 
			   select @Return=CONVERT(float,REPLACE(@werte,',','.'))
			else
			   select @Return=CONVERT(float,REPLACE(@werte,'-','.'))

	RETURN @Return

END
GO

und anschließend das VIEW erstellt:

CREATE VIEW View_varchar2float
AS
SELECT Zeit AS Zeit,  varchar2float(Wert) as Wert
	
FROM Test

GO

beim Lesen tritt trotzdem derselbe Fehler auf: "Error converting data type varchar to float"

Was mache ich den falsch ?

Danke im Voraus,

Grüße

Robin

[ebis]

Was mache ich den falsch ?

[HIGHLIGHT="SQL"]WITH Test (WERT)
AS ( SELECT '30-10'
UNION ALL SELECT '30E-10'
UNION ALL SELECT '11,797439'
UNION ALL SELECT '42393,59765'
UNION ALL SELECT '1339,7891845'
UNION ALL SELECT '9,997765E-03'
)
SELECT
CASE WHEN CHARINDEX ('-', Wert, 2) > 1
AND ISNUMERIC (REPLACE(Wert,'-','.')) = 1
THEN CONVERT(FLOAT (53), REPLACE(Wert,'-','.'))
ELSE CONVERT(FLOAT (53), REPLACE(Wert,',','.'))
END AS Wert
FROM Test[/HIGHLIGHT]