Until now, the operations with strings resumed to concatenation and its reverse operation(s) - extracting a substring or splitting a string into substrings. It was just the warm up! There are several other important operations that involve the internal manipulation of strings – insertion, deletion and replacement of a substring in a given string, operations performed using the Replace and Stuff functions.
Replace function, as its name denotes, replaces all occurrences of a specified string value with another. Several scenarios in which the function is quite useful: the replacement of delimiters,
special characters, correcting misspelled words or any other chunks of text. Here are some basic simple examples, following to consider the before mentioned applications in other posts:
-- examples with replace
DECLARE @str varchar(30)
SET @str = 'this is a test string'
SELECT replace(@str, ' ', ',') Example1
, replace(@str, ' ', ' ') Example2
, replace(@str, ' ', '') Example3
, replace(@str, 'is', 'as') Example4
Output:
Example1 |
Example2 |
Example3 |
Example4 |
this,is,a,test,string |
this is a test string |
thisisateststring |
thas as a test string |
When there are good chances that the searched string won’t appear in the “searched” string, and especially when additional logic is depending on the replacement, logic that could be included in the same expression with the replacement, then maybe it makes sense to check first if the searched character is present:
-- replacement with check
DECLARE @str varchar(30)
DECLARE @search varchar(30)
DECLARE @replacememt varchar(30)
SET @str = 'this is a test string'
SET @search = 'this string'
SET @replacememt = 'other string'
SELECT CASE
WHEN CharIndex(@search, @str)>0 THEN Replace(@str, @search, @replacememt)
ELSE @str
END result
Unfortunately the function doesn’t have the flexibility of the homonym functions provided by the languages from the family of VB (
VBScript,
VB.NET), which allow to do the replacement starting with a given position, and/or for a given number of occurrences. This type of behavior could be obtained with a simple trick – splitting the string into two other strings, performing the replacement on the second string, and then concatenating the first string and the result of the replacement:
-- replacement starting with a given position
DECLARE @str varchar(30)
DECLARE @search varchar(30)
DECLARE @replacememt varchar(30)
DECLARE @start int
SET @str = 'this is a test string'
SET @search = 's'
SET @replacememt = 'x'
SET @start = 7
SELECT Left(@str, @start-1) FirstPart
, RIGHT(@str, Len(@str)-@start+1) SecondPart
, CASE
WHEN @start <= LEN(@str) THEN Left(@str, @start-1) + Replace(RIGHT(@str, Len(@str)-@start+1), @search, @replacememt)
ELSE @str
END Replacement
Output:
FirstPart |
SecondPart |
Replacement |
this i |
s a test string |
this ix a text xtring |
The logic can be encapsulated in a function together with additional validation logic.
Stuff function inserts a string into another string starting with a given position and deleting a specified number of characters. Even if seldom used, the function it’s quite powerful allowing to insert a string in another, to remove a part of a string or more general, to replace a single occurrence of a string with another string, as can be seen from the below examples:
-- Stuff-based examples DECLARE @str varchar(30)
SET @str = 'this is a test string'
SELECT STUFF(@str, 6, 2, 'was ') Example1
, STUFF(@str, 1, 0, 'and ') Example2
, STUFF(@str, 1, 0, 'that') Example3
, STUFF(@str, LEN(@str) + 1, 0, '!') Example4
Output:
Example1 |
Example2 |
Example3 |
Example4 |
this was a test string |
and this is a test string |
thatthis is a test string |
NULL |
If in the first example is done a replacement of a text from a fix position, in the next examples are attempted insert on a first, middle respectively end position. As can be seen, the last example doesn’t work as expected, this because the insert position can’t go over the length of the target string. Actually, if the insert needs to be done at the beginning, respectively the end of a string, a concatenation can be much easier to use. A such example is the padding of strings with leading or trailing characters, typically in order to arrive to a given length. SQL Server doesn’t provide such a function, however the function is quite easy to build.
-- left/right padding DECLARE @str varchar(30)
DECLARE @length int
DECLARE @padchar varchar(1)
SET @str = '12345'
SET @length = 10
SET @padchar = '0'
SELECT @str StringToPad
, CASE
WHEN LEN(@str)<@length THEN Replicate(@padchar, @length-LEN(@str)) + @str
ELSE @str
END LeftPadding
, CASE
WHEN LEN(@str)<@length THEN @str + Replicate(@padchar, @length-LEN(@str))
ELSE @str
END RightPadding
Output:
StringToPad |
LeftPadding |
RightPadding |
12345 |
0000012345 |
1234500000 |
Happy Coding!