Substring vs Replace vs Stuff no SQL Server

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)

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)

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)

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.

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.

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.