/fdata%2F2470622%2Favatar-blog-1074652166-tmpphpjYbJuw.jpg){kind=avatar}
VBA Funktion VAL für den Microsoft Sql Server
In VBA = „Visual Basic for Application“ gibt es die Funktion VAL, von der ich mal sagen würde, sie ist so gerade eben noch deterministisch: Sie ermittelt aus ein String von links an die numerischen Zeichen bis zum ersten Nicht-Numerischen. Neben den Vorzeichen + und – wird nur noch der Punkt als Dezimaltrennzeichen akzeptiert; der aber fix unabhängig von Ländereinstellungen. Dies wird dann als Zahlenwert zurückgeliefert.
Wozu kann die Funktion nun nützlich sein? Um aus Texten die linksbündige Zahl zu ermitteln und das wiederum kann man nutzen, um etwas benutzerfreundlicher zu sortieren. Microsoft Windows Vista macht es schon so, das Zahlen innerhalb von Dateinamen als solche erkannt & für die Sortierung interpretiert werden, statt streng rein alphanumerisch zu sortieren.
Beispiel:
| Statt | Dessen |
| 1abc | 1abc |
| 10abc | 2efg |
| 2efg | 3hij |
| 20efg | 10abc |
| 200efg | 20efg |
| 3hij | 200efg |
| Zyx | Abc |
| Abc | Zyx |
Meine erste Implementierung der VAL Funktion setzt diese genau so um, wie in VBA definiert. Eine analoge Funktion, die die erste Zahl an beliebiger Stelle ermittelt, werde ich später noch umsetzen.
Die Sortierung nach den linksbündigen Zahlen sieht dann z.B. so aus: Erste Sortierung, ob VAL eine Zahle <> 0 liefert, dann nach dem Zahlenwert selbst und zum Schluß nach dem String; die wollen ja auch sortiert werden.
SELECT Wert
FROM Tabelle
ORDER BY CASE WHEN dbo.fnVAL(Wert) = 0
THEN 1 ELSE 0 END, dbo.fnVAL(Wert), Wert
Hier nun das Sql Script zur Anlage der Funktion „fnVAL“:
-- Alte Version vorher löschen IF NOT OBJECT_ID('dbo.fnVAL', 'FN') IS NULL
DROP FUNCTION dbo.fnVal GO CREATE FUNCTION dbo.fnVAL
(@value varchar(8000))
-- Autor: Olaf Helper -- Stand: 18.07.2009 -- Typ: User Defined Function -- Name: fnVAL -- Analog zur VBA Funktion: RETURNS sql_variant
AS BEGIN DECLARE @retun sql_variant, @loop int, @firstdot int; DECLARE @actualChar as char, @return sql_variant, @exit as bit; SET @return = 0; SET @firstdot = 0; SET @exit = 0; -- Eingabewert ohne Spaces SET @value = REPLACE(LTRIM(RTRIM(ISNULL(@value,'0'))), ' ', '');
-- Dezimaltrenner ist der Punkt, kein Komma
SET @value = REPLACE(@value, ',', 'x');
-- Vorzeichen vorhanden? -- Führende Null schadet nicht (keine Veränderung des Ergebnisse)
-- und erleichter das Auswerten
IF LEFT(@value, 1) = '-' OR LEFT(@value, 1) = '-'
BEGIN SET @loop = 2; SET @value = SUBSTRING(@value, 1, 1) + '0' + SUBSTRING(@value, 2, LEN(@value) - 1); END ELSE BEGIN SET @loop = 1; SET @value = '0' + @value; END SET @actualChar = SUBSTRING(@value, @loop, 1); WHILE @loop < LEN(@value) AND @exit = 0 AND (@actualChar LIKE '[0-9]' OR @actualChar ='.') AND (@firstdot = 0 OR (@firstdot >= 1 AND @actualChar <> '.')) BEGIN IF @actualChar = '.' AND @firstdot = 0 SET @firstdot = 1; SET @actualChar = SUBSTRING(@value, @loop + 1, 1); IF NOT (@actualChar LIKE '[0-9]' OR @actualChar ='.') SET @exit = 1; ELSE IF (@firstdot = 0 OR (@firstdot >= 1 AND @actualChar <> '.')) SET @loop = @loop + 1 ELSE SET @exit = 1; END SET @value = SUBSTRING(@value, 1, @loop); IF RIGHT(@value, 1) = '.' SET @loop = @loop - 1; -- Der numerische Teil ist soweit gefunden, nun konvertieren -- Mit Kommastelle? IF @firstdot <= 0 SET @return = CONVERT(bigint, SUBSTRING(@value, 1, @loop)); ELSE SET @return = CONVERT(float, SUBSTRING(@value, 1, @loop)); RETURN(@return); END;
GO -- Unit Test -- Zunächst einfache, die funktionieren müssen
SELECT dbo.fnVAL('1') UNION ALL SELECT dbo.fnVAL('11') UNION ALL
SELECT dbo.fnVAL('11xyz') UNION ALL
SELECT dbo.fnVAL('111.11,') UNION ALL
SELECT dbo.fnVAL('Hicks'); -- Nun Problemfälle
SELECT dbo.fnVAL(NULL) UNION ALL -- kann FN mit NULL umgehen?
SELECT dbo.fnVAL('.') UNION ALL -- Nur Dezimaltrenner?
SELECT dbo.fnVAL(' ') UNION ALL -- Nur leer?
SELECT dbo.fnVAL('') UNION ALL -- Nur leerer?
SELECT dbo.fnVAL(',') UNION ALL -- Nur Anti-Dezimaltrenner? SELECT dbo.fnVAL('1-1') UNION ALL -- Mit Vorzeichen drin(soll 1 ergeben) SELECT dbo.fnVAL('-') UNION ALL -- Nur Vorzeichen? SELECT dbo.fnVAL('...') -- Pünktchen?
/idata%2F2470622%2FDESCRIBE_SC1.jpg)
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
