Transact-SQL string and datetime functions

String functions in MS SQL SERVER

Here in order to double the number of searching characters the concatenation @str+@str was applied. However for this effect we can use one more function - REPLICATE that repeats the first argument so many times as it specified by the second argument.

SELECT name, (LEN(REPLACE(name, @str, REPLICATE(@str, 2))) — 
 LEN(name))/LEN(@str) 
FROM Ships

That is we repeat twice the substring stored in the variable @str.

If we need to replace in a string not a certain sequence of characters, but a specified number of characters, starting from some position, it's simpler to use the STUFF function:

STUFF (character_expression1 , start , length , character_expression2)

This function replaces a substring with length of length that starts from the start position in the character_expression1 with the character_expression2.

Example 7.2.1

Change the name of a ship: append "_" (underscore) and a launching year after the first 5 characters. If there is less than 5 characters in the name, complement it with the spaces.

This task can be solved with many different functions. We'll try to do it with the STUFF function. In the first approach we'll write (bounding with the select query):

Console

Execute

SELECT name, STUFF(name, 6, LEN(name), '_'+launched) 
FROM Ships

SELECT name, STUFF(name, 6, LEN(name), '_'+launched) FROM Ships

    Scheme

I use LEN(name) as the third argument (number of characters to replace), because I should replace all the characters until the end of the string, and that's why I take it with a reserve - the original number of characters in the name. But this query will still return an error. And the question is not in the third argument, but in the forth, where the concatenation of the string constant and the integer column is performing. This is the type conversion error. In order to convert a number to its string representation we may use one more built-in function - STR.

Parameter L of STUFF function is integer, so it can obtain negative and zeroth values. For the negative values STUFF function returns NULL, as in the case when the second parameter exceeds the length of string1. The value of 0 implies insertion of string2 into string1 starting from position given by the second parameter.

Example 7.2.2

Supply a delimiter "-" in character representation of a date in the format YYYYMMDD

Console

Execute

SELECT STUFF(STUFF('20121119',5,0,'-'),8,0,'-')

select STUFF(STUFF('20121119',5,0,'-'),8,0,'-')

    Scheme

No scheme