loading..
Русский    English
02:56

REPLICATE and STUFF functions

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.

  1. SELECT name, (LEN(REPLACE(name, @str, REPLICATE(@str, 2)))
  2. LEN(name))/LEN(@str)
  3. 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:

  1. 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
  1. SELECT name, STUFF(name, 6, LEN(name), '_'+launched)
  2. FROM Ships

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
  1. SELECT STUFF(STUFF('20121119',5,0,'-'),8,0,'-')

Bookmark and Share
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100