Substring vs. Replace vs. Stuff in SQL Server

Arbeiten mit Textzeichenfolgen und Vergleichen der Verwendung von SUBSTRING, REPLACE und STUFF neben einigen anderen Zeichenfolgenfunktionen in SQL Server

Beispiel

Unter Verwendung unseres Domainnamens haben wir ein Beispiel für das Entfernen von „https://“ auf drei Arten bereitgestellt.

Wir haben einige Test-SQL als Beispiel bereitgestellt und werden die folgenden eingebauten Funktionen untersuchen;

TEILZEICHENFOLGE
ERSETZEN
SACHEN
LINKS RECHTS
CHARINDEX
UMKEHREN
ISNULL/NULLIF

SQL

DECLARE @e NVARCHAR(MAX)='https://www.claytabase.co.uk'SELECT SUBSTRING(@e,9,100)SELECT REPLACE(@e,'https://','')SELECT STUFF(@e,1,8,'')--www.claytabase.co.uk--www.claytabase.co.uk--www.claytabase.co.uk

TEILZEICHENFOLGE

Verwenden Sie SUBSTRING, um einen Teil einer Zeichenfolge anzuzeigen. In diesem Beispiel möchten wir also, dass die Startposition 9 ist. Verwenden Sie längere Längen, wenn Sie den Rest des Endes der Zeichenfolge anzeigen möchten.

SUBSTRING(Ausdruck, Start, Länge)

LINKS RECHTS

Sie können natürlich LEFT und RIGHT verwenden, um den linken und rechten Teil der Zeichenfolge anzuzeigen. In Kombination mit REVERSE und CHARINDEX können Sie ziemlich komplexe Suchen durchführen, z. B. das letzte Vorkommen einer Zeichenfolge oder ein nummeriertes (wie das dritte) Vorkommen finden.

ERSETZEN

Verwenden Sie REPLACE, um alle Vorkommen einer Zeichenfolge innerhalb einer Zeichenfolge zu ersetzen. Wenn Sie nur ein einzelnes Vorkommen ersetzen möchten, verwenden Sie STUFF.

REPLACE(string_expression, string_pattern, string_replacement)

SACHEN

Verwenden Sie STUFF, um einen Teil einer Textzeichenfolge durch einen anderen zu ersetzen, bei dem Sie die Positionen der Zeichen kennen, die Sie ersetzen möchten.

STUFF(Zeichenausdruck, Anfang, Länge, Ersetzungsausdruck)

Verwenden Sie die CHARINDEX-Funktion, um die Position zu finden

Es ist höchst unwahrscheinlich, dass Sie die Position der Zeichenfolge, die Sie ersetzen möchten, ständig kennen, daher verwenden wir normalerweise die CHARINDEX-Funktion, um die Position zu finden.

Unten haben wir es verwendet, um dieselbe Zeichenfolge mit SUBSTRING und RIGHT auseinander zu ziehen, aber die Optionen sind in Bezug auf die Kombinationen, die verwendet werden können, ziemlich grenzenlos.

Wenn Sie CHARINDEX als Startposition verwenden, müssen Sie 1 zum Wert addieren oder subtrahieren, um das richtige Anzeigeelement zu erhalten.

SQL

DECLARE @e NVARCHAR(MAX)='https://www.claytabase.co.uk'SELECT CHARINDEX('.',@e,1),SUBSTRING(@e,CHARINDEX('.',@e,1)+1,100)SELECT CHARINDEX('.',@e,CHARINDEX('.',@e,1)+1),SUBSTRING(@e,CHARINDEX('.',@e,CHARINDEX('.',@e,1)+1)+1,100)SELECT CHARINDEX('.',REVERSE(@e),1),RIGHT(@e,CHARINDEX('.',REVERSE(@e),1)-1)--12,claytabase.co.uk--23,co.uk--3,uk

Fehler umgehen

Die Verwendung der Addition oder Subtraktion von 1 zusammen mit den anderen Funktionen kann dazu führen, dass ein negativer Wert analysiert wird, was zu einem Fehler führt. Im Beispiel haben wir dies umkodiert, indem wir -1 auf NULL gesetzt haben, was dann implizit in 0 umgewandelt würde.

Workaround

DECLARE @e NVARCHAR(MAX)='https://www.claytabase.co.uk'SELECT CHARINDEX('x',REVERSE(@e),1),NULLIF(RIGHT(@e,CHARINDEX('x',REVERSE(@e),1)),-1)

Kombinieren verschiedener Zeichenfolgenfunktionen

Unter Verwendung der verschiedenen obigen Funktionen haben wir die verschiedenen Teile einer URL-Zeichenfolge unten herausgezogen.

Diese ziehen das Protokoll, die Domäne, die Seite und die Abfragezeichenfolgen heraus, falls vorhanden.

Complex SQL

DECLARE @e NVARCHAR(MAX)='https://www.claytabase.co.uk/Business-Solutions/Database-Consultancy-Services/Technical-Articles/SQL-Server-Data/Substring-vs-Replace-vs-Stuff?qrystr=claytabase'SELECT CHARINDEX('//',@e,1),LEFT(@e,CHARINDEX('//',@e,1)+1)--Protocol add 1 to pull through complete valueSELECT CHARINDEX('//',@e,1),CHARINDEX('/',@e,CHARINDEX('//',@e,1)+2),SUBSTRING(@e,CHARINDEX('//',@e,1)+2,CHARINDEX('/',@e,10)-LEN(LEFT(@e,CHARINDEX('//',@e,1)+2)))--CHARINDEX needs adjusting to remove protocol lengthSELECT CHARINDEX('/',@e,CHARINDEX('//',@e,1)+2),ISNULL(NULLIF(CHARINDEX('?',@e,1)-1,-1),LEN(@e)),SUBSTRING(@e,CHARINDEX('/',@e,CHARINDEX('//',@e,1)+2)+1,ISNULL(NULLIF(CHARINDEX('?',@e,1)-1,-1),LEN(@e))-CHARINDEX('/',@e,CHARINDEX('//',@e,1)+2))SELECT CHARINDEX('?',REVERSE(@e),1),RIGHT(@e,ISNULL(NULLIF(CHARINDEX('?',REVERSE(@e),1)-1,-1),0))--7,https://--7,29,www.claytabase.co.uk--29,123,Database-Consultancy-Services/Technical-Articles/SQL-Server-Data/Substring-vs-Replace-vs-Stuff--18,qrystr=claytabaseSET @e='http://www.claytabase.co.uk/'SELECT CHARINDEX('//',@e,1),LEFT(@e,CHARINDEX('//',@e,1)+1)--Protocol add 1 to pull through complete valueSELECT CHARINDEX('//',@e,1),CHARINDEX('/',@e,CHARINDEX('//',@e,1)+2),SUBSTRING(@e,CHARINDEX('//',@e,1)+2,CHARINDEX('/',@e,10)-LEN(LEFT(@e,CHARINDEX('//',@e,1)+2)))--Domain needs adjusting to remove protocolSELECT CHARINDEX('/',@e,CHARINDEX('//',@e,1)+2),ISNULL(NULLIF(CHARINDEX('?',@e,1)-1,-1),LEN(@e)),SUBSTRING(@e,CHARINDEX('/',@e,CHARINDEX('//',@e,1)+2)+1,ISNULL(NULLIF(CHARINDEX('?',@e,1)-1,-1),LEN(@e))-CHARINDEX('/',@e,CHARINDEX('//',@e,1)+2))SELECT CHARINDEX('?',REVERSE(@e),1),RIGHT(@e,ISNULL(NULLIF(CHARINDEX('?',REVERSE(@e),1)-1,-1),0))--6,http://--6,28,www.claytabase.co.uk--28,28,--0,