Tag Archive for 'MSSQL'

Split Funktion mittels CLR

Published on 3. September 2008 in ASP.Net 2, ASP.Net 3 / ASP.Net 3.5, Allgemein, SQL Server and Visual Studio. 0 Comments Tags: , , , , , , .

Eine beliebte Stringfunktion fehler leider nach wie vor im SLQ Server: Split. Das hat sich auch mit dem neuen SQL Server 2008 immer noch nicht geändert. Ich hatte ja schon mal eine Splitfunktion gepostet, möchte dies aber jetzt als CLR Funktion implementieren. Lange Reder kurzer Sinn:

  • Man erstellt im Visual Studio ein neues Datenbankprojekt.
  • In den Projekteinstellungen sollte die Assembly signiert werden (Einstellungen – Signing – Sign the assembly)
  • Unter dem Karteireiter “Database” kann vorerst das Permissionlevel auf Safe bleiben, da wir ja keine externen Resources ansprechen wollen.
    Safe = Zugriff auf Daten des eigenen SQL Servers über den In-process Managed Provider
    External = Zugriff auf externe Resourcen ist erlaubt
    Unsafe = Es kann unmanaged Code ausgeführt werden
    Erhält man eine Fehlermeldung beim Bereitstellen der Assembly, kann es daran liegen das der Datenbank nicht vertraut wird. Dann sollte man die Trustworthy Eigenschaft setzen:

    Alter Database [DB] Set Trustworthy on
  • Man fügt dem Projekt eine neue Klasse hinzu, ich habe sie einfach “Stringfunctions” genannt.
  • Dieser Klasse fügen wir eine Prozedur hinzu: 
        <SqlProcedure()> _
    Public Shared Sub Split(ByVal Input As SqlString, ByVal Delimiter As SqlString)
        Dim pipe As SqlPipe = SqlContext.Pipe
        Dim splittet() As String = Input.Value.Split(Delimiter.Value.ToCharArray)
        Dim reslist As New List(Of SqlMetaData)
        For i As Integer = 0 To splittet.Length - 1
            reslist.Add(New SqlMetaData("Col" + i.ToString(), SqlDbType.NVarChar, 4000))
        Next
        Dim result As New SqlDataRecord(reslist.ToArray())
        For i As Integer = 0 To splittet.Length - 1
            result.SetString(i, splittet(i))
        Next
        pipe.Send(result)
    End Sub

    Das Attribut SqlProcedure() sorgt dafür, das beim Bereitstellen automatisch eine neue Stored Procedure angelegt wird. Als Parameter der Prozedur übergeben wir zwei SQLStrings: Input und Delimiter. Wofür diese stehen sagen die Namen ja schon… ;) Um die Anzahl der notwendigen Spalten in unserer Ergnistabelle zu ermitteln legen wir uns ersteinmal eine neue Liste mit Spaltendefinitionen an. Danach wird unsere Ergebnistabelle erstellt: 

    Dim result As New SqlDataRecord(reslist.ToArray())

    Jetzt können wir endlich alle Elemente aus dem Array in die Ergebnistabelle schreiben und zurückgeben:

    For i As Integer = 0 To splittet.Length - 1
	result.SetString(i, splittet(i))
Next
pipe.Send(result)

    Die Verwendung der Funktion ist denkbar einfach: 

    exec Split 'feld1;feld2;feld3;feld4;feld5;feld6;feld7',';'

    Und liefert uns eine Tabelle zurück:

     Col0	Col1	Col2	Col3	Col4	Col5	Col6
 feld1	feld2	feld3	feld4	feld5	feld6	feld7

Split Funktion für MSSQL 2000

Published on 11. April 2008 in SQL Server. 0 Comments Tags: , , , , .

Kann man immer wieder mal brauchen: Eine Split-Funktion für MSSQL (ab 2000).

CREATE FUNCTION [dbo].[udf_ItemExtract]
(
 @Position INTEGER,
 @List VARCHAR(2000),  --VARCHAR(MAX)
 @Delimiter VARCHAR(1)
)
RETURNS VARCHAR(2000)  --VARCHAR(MAX)
AS
BEGIN
 DECLARE @RetVal VARCHAR(2000) SET @RetVal = ''  --VARCHAR(MAX)
 DECLARE @Counter  INT SET @Counter = 0
 DECLARE @StartPos INT
 DECLARE @Length INT
 DECLARE @DelimPos INT SET @DelimPos = 0
 -- SCAN THROUGH UNTIL WE FIND THE ITEM AT THE POSITION OF THE INDEX - WE'LL GO THROUGH WHILE LOOP AT LEAST ONCE
 WHILE @Counter < @Position
  BEGIN
   SET @Counter = @Counter + 1   -- increment counter
   SET @StartPos = @DelimPos + 1 -- move the start position to right after previously found delimiter (or 1st char if this is the fist time through
   SET @DelimPos = CHARINDEX(@Delimiter,@List,@DelimPos + 1)   --find the next delimiter
   -- IF THERE ARE NO DELIMITERS LEFT...
   IF @DelimPos = 0
   BEGIN
    -- IF WE'RE ON THE POSITION WE'RE LOOKING FOR, THEN SET LENGTH TO THE REST OF THE STRING
    IF @Counter = @Position SET @Length = (LEN(@List)+1)-@StartPos
    -- OTHERWISE SET LENGTH TO -1 AND THAT WILL BE AN INDICATOR TO RETURN EMPTY STRING
    ELSE SET @Length = -1
   END
   ELSE SET @Length = @DelimPos - @StartPos  -- otherwise length is now from new start pos right up to delimiter
  END
  -- RETURN THE EXTRACTED STRING
  IF @Length > 0 SET @RetVal = SUBSTRING(@List,@StartPos,@Length)
  RETURN @RetVal
END