Original von Narayana Vyas Kondreddi; deutsche Übersetzung von Frank Kalis

Neulich stellte jemand in den öffentlichen Microsoft Newsgroups die Frage, wie man Zeichen innerhalb eines Strings sortiert? Zum Beispiel enthielt der String 'CBA' und er wollte nun die Zeichen innerhalb des String sortieren, um daraus 'ABC' zu machen. Anstelle nun die prozedurale Lösung zu verwenden, habe ich versucht, rein aus Spaß, dies mit einem relationalen Ansatz (T-SQL spezifisch) zu lösen. In diesem Artikel zeige ich Ihnen, wie Sie eine Zahlentabelle verwenden können, um diese innovative Art von Abfragen zu schreiben.

Lassen Sie uns zunächst einmal eine Zahlentabelle mit Namen 'Numbers' erstellen. Diese enthält genau eine Spalte namens 'Number'. Diese 'Number' Spalte ist eine IDENTITY Spalte für die ein Primary Key und clustered Index definiert wurde. Ein Clustered Index erhöht in diesem Fall die Performance der Abfragen, da wir diese Numberstabelle nach Zahlenbereichen abfragen werden.

Das folgende Skript löscht die 'Numbers' Tabelle, falls sie bereits existiert, erstellt sie und füllt sie mit den Zahlen von 1 bis 8000 (Wir gebrauchen nur 8.000 Zahlen, da eine CHAR/VARCHAR Variable oder Spalte nur maximal 8.000 Zeichen lang sein kann). Beachten Sie die Verwendung von "DEFAULT VALUES". So sollte man Zeilen in eine Tabelle mit nur einer Spalte einfügen. Und diese Spalte ist zufällig eine IDENTITY Spalte. Ich verwende die IDENTITY Spalte hier aus Bequemlichkeit und Vereinfachungsgründen . Nichts sollte Sie abhalten, eine SmallInt Spalte zu verwenden, und 8.000 Zeilen einzufügen, indem Sie eine Variable erhöhen.

SET NOCOUNT ON
GO

IF EXISTS
(
	SELECT	1
	FROM 	INFORMATION_SCHEMA.TABLES
	WHERE		TABLE_NAME 	= 'Numbers'
	    	AND 	TABLE_SCHEMA 	= 'dbo'
	    	AND 	TABLE_TYPE 	= 'BASE TABLE'
)
BEGIN
	DROP TABLE dbo.Numbers
END
GO

CREATE TABLE dbo.Numbers
(
	Number smallint IDENTITY(1, 1) PRIMARY KEY
)
GO

WHILE 1 = 1
BEGIN
	INSERT INTO dbo.Numbers DEFAULT VALUES
	
	IF @@IDENTITY = 8000 
	BEGIN
		BREAK
	END
END
GO

Nun können wir das vorliegende Problem angehen: "Sortierung der Zeichen in einem String."

Das nachfolgende Skript verwendet die Zahlentabelle, um den String in die einzelnen Zeichen aufzubrechen und eine abgeleitete Tabelle zu erstellen. Aus dieser wird dann ein sortierter String erzeugt durch Einsatz einer aggregierten Verknüpfungsabfrage. Dies ist ein wesentlich besserer Ansatz, als das Sie einen Sortieralgorithmus in Ihren Skripten implementieren müssten. Warum das Rad neu erfinden, wenn SQL Server die Sortierung erledigen kann?

DECLARE @input varchar(100), @output varchar(100), @len smallint
SET @input = 'CDBEA'
SET @output = ''
SET @len = LEN(@input)

SELECT @output = @output + Val 
FROM 
(
	SELECT TOP 100 PERCENT SUBSTRING(@input, Number, 1) AS Val
	FROM dbo.Numbers (NOLOCK)
	WHERE Number <= @len
	ORDER BY Val
) AS Derived

SELECT	@input AS 'Original string', 
	@output AS 'Sorted string'

Diese Grundidee kann man für andere Szenarien erweitern. Zum Beispiel, um eindeutige Zeichen aus einem gegebenen String zu extrahieren? Also, um aus einem Input 'abbcccdddd', den Output von t 'abcd' zu erhalten. Das folgende Skript macht genau das unter Verwendung des DISTINCT Schlüsselwortes. Dieser Ansatz macht von SQL Server's Fähigkeit Gebrauch, eindeutige Zeilen aus einer vorgegebenen Menge zurückzugeben. Müssten Sie diese Fähigkeit selber implementieren, wäre dies eine komplizierte Aufgabe.

DECLARE @input varchar(100), @output varchar(100), @len smallint
SET @input = 'ABBCCCDDDD'
SET @output = ''
SET @len = LEN(@input)

SELECT @output = @output +  Val
FROM
(
	SELECT DISTINCT TOP 100 PERCENT SUBSTRING(@input, Number, 1) AS Val
	FROM dbo.Numbers (NOLOCK)
	WHERE Number <= @len
	ORDER BY Val
) AS Derived

SELECT	@input AS 'Original string',
	@output AS 'Sorted string with UNIQUE characters only'

Hier ist noch ein weiteres Szenario! Wie wäre es, nur die Zahlen aus einem vorgegebenen String zu erhalten? Erinnern Sie sich noch an diese Telefone, die die Zahlen aus SMS/Text Nachrichten herausgezogen haben? :-) Natürlich kann dieses Problem leicht mit einem prozeduralen Ansatz gelöst werden, indem Sie mit einer Schleife vom Anfang des String bis zum Ende gehen, und alle nicht-numerischen Zeichen eliminieren. Die Performance Unterschiede zwischen beiden Ansätzen sollten minimal sein. Ich habe kein Benchmarking durchgeführt, aber ich werde diesen Artikel aktualisieren, wenn ich dies mache. Wie auch immer, hier ist der Code:

DECLARE @input varchar(100), @output varchar(100), @len smallint
SET @input = 'My Number is: 0771 543 2360'
SET @output = ''
SET @len = LEN(@input)

SELECT @output = @output +  Val
FROM
(
	SELECT TOP 100 PERCENT Number, SUBSTRING(@input, Number, 1) AS Val
	FROM dbo.Numbers (NOLOCK)
	WHERE Number <= @len
	ORDER BY Number
) AS Derived
WHERE Val LIKE '[0-9]' 

SELECT	@input AS 'Original string',
	@output AS 'Extracted numbers'

Verwenden Sie SQL Server 2000, so können Sie die obigen Skripte zu nützlichen User Defined Functions (UDF) konvertiert werden. Ich sage "nützlich", da UDFs an verschiedenen Stellen verwendet werden können, wie z.B. in einer Spaltenliste oder in einer WHERE Klausel eines SELECT Statements. Hier sind die User Defined Function Skripte:

User Defined Function 1: SortString()

IF EXISTS
(
	SELECT	1
	FROM	INFORMATION_SCHEMA.ROUTINES
	WHERE		ROUTINE_NAME 	= 'SortString'
		AND	ROUTINE_SCHEMA	= 'dbo'
		AND	ROUTINE_TYPE	= 'FUNCTION'
)
BEGIN
	DROP FUNCTION dbo.SortString
END
GO

CREATE FUNCTION dbo.SortString
(
	@input varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
	DECLARE @output varchar(8000), @len smallint
	SET @output = ''
	SET @len = LEN(@input)

	SELECT @output = @output + Val 
	FROM 
	(
		SELECT TOP 100 PERCENT SUBSTRING(@input, Number, 1) AS Val
		FROM dbo.Numbers (NOLOCK)
		WHERE Number <= @len
		ORDER BY Val
	) AS Derived

	RETURN @output
END
GO

SELECT dbo.SortString('911Abcdzyxfghjie999') AS 'Sorted string'
GO

User Defined Function 2: ExtractUniqueChars()

IF EXISTS
(
	SELECT	1
	FROM	INFORMATION_SCHEMA.ROUTINES
	WHERE		ROUTINE_NAME 	= 'ExtractUniqueChars'
		AND	ROUTINE_SCHEMA	= 'dbo'
		AND	ROUTINE_TYPE	= 'FUNCTION'
)
BEGIN
	DROP FUNCTION dbo.ExtractUniqueChars
END
GO

CREATE FUNCTION dbo.ExtractUniqueChars
(
	@input varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
	DECLARE @output varchar(8000), @len smallint
	SET @output = ''
	SET @len = LEN(@input)

	SELECT @output = @output +  Val
	FROM
	(
		SELECT DISTINCT TOP 100 PERCENT SUBSTRING(@input, Number, 1) AS Val
		FROM dbo.Numbers (NOLOCK)
		WHERE Number <= @len
		ORDER BY Val
	) AS Derived

	RETURN @output
END
GO

SELECT dbo.ExtractUniqueChars('cba abc bac') AS 'Unique characters (Sorted)'
GO

User Defined Function 3: ExtractNumbers()

IF EXISTS
(
	SELECT	1
	FROM	INFORMATION_SCHEMA.ROUTINES
	WHERE		ROUTINE_NAME 	= 'ExtractNumbers'
		AND	ROUTINE_SCHEMA	= 'dbo'
		AND	ROUTINE_TYPE	= 'FUNCTION'
)
BEGIN
	DROP FUNCTION dbo.ExtractNumbers
END
GO

CREATE FUNCTION dbo.ExtractNumbers
(
	@input varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
	DECLARE @output varchar(8000), @len smallint
	SET @output = ''
	SET @len = LEN(@input)

	SELECT @output = @output +  Val
	FROM
	(
		SELECT TOP 100 PERCENT Number, SUBSTRING(@input, Number, 1) AS Val
		FROM dbo.Numbers (NOLOCK)
		WHERE Number <= @len
		ORDER BY Number
	) AS Derived
	WHERE Val LIKE '[0-9]' 
	
	RETURN @output
END
GO
	
SELECT dbo.ExtractNumbers('My Number is: 0771 574 0609')
GO

Falls Sie ein paar Kilobyte Arbeitsspeicher übrig haben, können Sie diese Numbers Tabelle im Speicher pinnen, so daß die Seiten dieser Tabelle im Arbeitsspeicher verbleiben, nachdem sie dorthin geladen wurden. Das ist in Ordnung für kleinere Tabellen wie die 'Numbers' Tabelle. Versuchen Sie es aber nicht mit großen Tabellen, da dies negativ die Performance von SQL Server beeinflußen kann. Der folgende Befehl pinnt die 'Numbers' Tabelle im Speicher (Sie können auch unter DBCC PINTABLE in den SQL Server Books Online (BOL) nachschlagen):

EXEC sp_tableoption 'Numbers', 'pintable', 
'true'
GO

In den meisten Fällen ist es nicht notwendig, eine Tabelle im Speicher zu pinnen, auf die häufig zugegriffen wird, und SQL Server ausreichend Datencache hat, um die Tabelle im Speicher zu halten.

Hiermit endet dieser Artikel. Ich habe mich auf Stringmanipulationen beschränkt, aber mit Sicherheit können Sie noch wesentlich mehr mit einer Zahlentabelle erledigen. Achten Sie auf weitere Artikel und Code Beispiel in nächster Zeit in meiner code library. Viel Spaß!

Den Originalartikel und viele weitere finden Sie hier