SQL Server/T-SQL Tutorial/String Functions/STUFF

Материал из SQL эксперт
Перейти к: навигация, поиск

Stuffing a String into a String

   <source lang="sql">

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></source>


STUFF(notes, PATINDEX("%computer[^s]%", notes),

   <source lang="sql">

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</source>


The syntax for STUFF is STUFF(string_used_as_basis, start_point, length, string_to_insert)

   <source lang="sql">

5> 6> SELECT STUFF("ABCDEFGHIJKL",2,3,"sho") 7> GO

AshoEFGHIJKL (1 rows affected)</source>

Источник — «http://sqle.ru/index.php?title=SQL_Server/T-SQL_Tutorial/String_Functions/STUFF&oldid=6251»