Microsoft SQL Server
Die STUFF-Funktion
Suche…
Parameter
Parameter | Einzelheiten |
---|---|
Zeichenausdruck | die vorhandene Zeichenfolge in Ihren Daten |
Startposition | die Position in character_expression , um die length zu löschen und dann die replacement_string einzufügen |
Länge | Die Anzahl der Zeichen, die aus character_expression gelöscht werden sollen |
replace_string | Die Zeichenfolge, die in character_expression eingefügt werden soll |
Grundzeichen ersetzen mit STUFF ()
Die Funktion STUFF()
fügt eine Zeichenfolge in eine andere Zeichenfolge ein, indem zunächst eine angegebene Anzahl von Zeichen gelöscht wird. Im folgenden Beispiel wird "Svr" gelöscht und durch "Server" ersetzt. Dies geschieht durch Angabe der start_position
und der length
der Ersetzung.
SELECT STUFF('SQL Svr Documentation', 5, 3, 'Server')
Wenn Sie dieses Beispiel SQL Svr Documentation.
wird die SQL Server Documentation
anstelle der SQL Svr Documentation.
Verwenden von FOR XML zum Verketten von Werten aus mehreren Zeilen
Eine übliche Verwendung der FOR XML
Funktion besteht darin, die Werte mehrerer Zeilen zu verketten.
Hier ein Beispiel mit der Customers-Tabelle :
SELECT
STUFF( (SELECT ';' + Email
FROM Customers
where (Email is not null and Email <> '')
ORDER BY Email ASC
FOR XML PATH('')),
1, 1, '')
Im obigen Beispiel wird FOR XML PATH(''))
zum Verketten von E-Mail-Adressen verwendet ;
als Trennzeichen. Der Zweck von STUFF
besteht auch darin, die führende STUFF
zu entfernen ;
von der verketteten Zeichenfolge. STUFF
setzt den verketteten String implizit auch von XML in varchar um.
Hinweis: Das Ergebnis des obigen Beispiels wird XML-codiert, dh es werden <
Zeichen durch <
Wenn Sie dies nicht möchten, ändern Sie FOR XML PATH(''))
in FOR XML PATH, TYPE).value('.[1]','varchar(MAX)')
, zB:
SELECT
STUFF( (SELECT ';' + Email
FROM Customers
where (Email is not null and Email <> '')
ORDER BY Email ASC
FOR XML PATH, TYPE).value('.[1]','varchar(900)'),
1, 1, '')
Dies kann verwendet werden, um ein ähnliches Ergebnis wie GROUP_CONCAT
in MySQL oder string_agg
in PostgreSQL 9.0+ zu erzielen, obwohl anstelle von GROUP BY-Aggregaten Unterabfragen verwendet werden. (Alternativ können Sie ein benutzerdefiniertes Aggregat wie dieses installieren, wenn Sie eine Funktionalität suchen, die der von GROUP_CONCAT
)
Spaltennamen mit Komma trennen (keine Liste)
/*
The result can be use for fast way to use columns on Insertion/Updates.
Works with tables and views.
Example: eTableColumns 'Customers'
ColumnNames
------------------------------------------------------
Id, FName, LName, Email, PhoneNumber, PreferredContact
INSERT INTO Customers (Id, FName, LName, Email, PhoneNumber, PreferredContact)
VALUES (5, 'Ringo', 'Star', '[email protected]', NULL, 'EMAIL')
*/
CREATE PROCEDURE eTableColumns (@Table VARCHAR(100))
AS
SELECT ColumnNames =
STUFF( (SELECT ', ' + c.name
FROM
sys.columns c
INNER JOIN
sys.types t ON c.user_type_id = t.user_type_id
WHERE
c.object_id = OBJECT_ID( @Table)
FOR XML PATH, TYPE).value('.[1]','varchar(2000)'),
1, 1, '')
GO
Zeug für Komma getrennt in SQL Server
FOR XML PATH
und STUFF
zum Verketten der mehreren Zeilen in einer einzigen Zeile:
select distinct t1.id,
STUFF(
(SELECT ', ' + convert(varchar(10), t2.date, 120)
FROM yourtable t2
where t1.id = t2.id
FOR XML PATH (''))
, 1, 1, '') AS date
from yourtable t1;
Grundbeispiel der STUFF () - Funktion.
STUFF (Originalausdruck, Start, Länge, Ersetzungsausdruck)
Die Funktion STUFF () fügt Replacement_expression an der angegebenen Startposition zusammen mit dem Entfernen der mit dem Parameter Length angegebenen Zeichen ein.
Select FirstName, LastName,Email, STUFF(Email, 2, 3, '*****') as StuffedEmail From Employee
Wenn Sie dieses Beispiel ausführen, wird die angegebene Tabelle zurückgegeben
Vorname | Nachname | StuffedEmail | |
---|---|---|---|
Jomes | Jäger | [email protected] | J*****[email protected] |
Shyam | Rathod | [email protected] | S*****[email protected] |
RAM | shinde | [email protected] | R ***** hotmail.com |