SQLServerの部分文字列と置換とスタッフ

テキスト文字列を操作し、SUBSTRING、REPLACE、STUFFの使用を、SQLServerの他の文字列関数と比較します。

Example

Using our domain name, we have provided an example of removing "https://" three ways.

We've provided some test SQL as an example and will explore the following built in functions;

SUBSTRING
REPLACE
STUFF
LEFT/RIGHT
CHARINDEX
REVERSE
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

SUBSTRING

Use SUBSTRING to display part of a string. So in this example we want the starting position to be 9. Use longer lengths when you want to display the rest of the end of the string.

SUBSTRING(expression, start, length)

LEFT & RIGHT

You can of course use LEFT and RIGHT to display the left and right parts of the string. When combined with REVERSE and CHARINDEX you can perform fairly complex searches like finding the last instance of a string, or a numbered (like the third) occurrence.

REPLACE

Use REPLACE to replace all occurrences of a string within a string. When you want to replace only a single occurrence, use STUFF.

REPLACE(string_expression, string_pattern, string_replacement)

STUFF

Use STUFF to replace part of a text string with another where you know the positions of the characters you wish to replace.

STUFF(character_expression, start, length, replace_expression)

Use CHARINDEX function to find the position

It is highly unlikely you will know the position of the string you want to replace all of the time, so we normally use the CHARINDEX function to find the position.

Below we have used it to pull apart the same string with SUBSTRING and RIGHT, but the options are pretty much limitless in terms of combinations that can be used.

When using CHARINDEX as a starting position you will need to add or subtract 1 from the value to get the right display item.

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

Working around failures

Using the addition or subtraction of 1 along side the other functions may result in a negative value being parsed, which will cause a failure. In the example we have coded around this by setting -1 to NULL, which would then be implicitly converted to 0.

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)

Combining various string functions

Using the various functions above, we have pulled out the various parts of a URL string below.

These will pull out the protocol, domain, page and query strings if they exist.

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,