SQL Server/T-SQL Tutorial/String Functions/STUFF
Stuffing a String into a String
4>
5> SELECT STUFF ( "www.sqle.ru""s name is sqle. ",
6> 8,
7> 1,
8> "Edgar" )
9> GO
-------------------------------------
www.javEdgar2s.ru"s name is sqle.
(1 rows affected)
1>
STUFF(notes, PATINDEX("%computer[^s]%", notes),
4> CREATE TABLE titles(
5> title_id varchar(20),
6> title varchar(80) NOT NULL,
7> type char(12) NOT NULL,
8> pub_id char(4) NULL,
9> price money NULL,
10> advance money NULL,
11> royalty int NULL,
12> ytd_sales int NULL,
13> notes varchar(200) NULL,
14> pubdate datetime NOT NULL
15> )
16> GO
1>
2> insert titles values ("1", "Secrets", "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94")
3> insert titles values ("2", "The", "business", "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91")
4> insert titles values ("3", "Emotional", "psychology", "0736", $7.99, $4000.00, 10, 3336,"Note 3","06/12/91")
5> insert titles values ("4", "Prolonged", "psychology", "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91")
6> insert titles values ("5", "With", "business", "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91")
7> insert titles values ("6", "Valley", "mod_cook", "0877", $19.99, $0.00, 12, 2032,"Note 6","06/09/91")
8> insert titles values ("7", "Any?", "trad_cook", "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91")
9> insert titles values ("8", "Fifty", "trad_cook", "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91")
10> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1> UPDATE titles
2> SET notes=STUFF(notes, PATINDEX("%computer[^s]%", notes),
3> DATALENGTH("computer"), "Hi-Tech Computers")
4> WHERE PATINDEX("%computer[^s]%", notes) > 0
5> GO
(0 rows affected)
1>
2>
3> drop table titles;
4> GO
The syntax for STUFF is STUFF(string_used_as_basis, start_point, length, string_to_insert)
5>
6> SELECT STUFF("ABCDEFGHIJKL",2,3,"sho")
7> GO
------------
AshoEFGHIJKL
(1 rows affected)