Overblog Folge diesem Blog
Edit post Administration Create my blog
Blog von Olaf Helper

VBA Funktion VAL für den Microsoft Sql Server

21. Juli 2009 , Geschrieben von Olaf Helper Veröffentlicht in #T-SQL

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? 

Diesen Post teilen

Repost 0
Um über die neuesten Artikel informiert zu werden, abonnieren:

Kommentiere diesen Post