Sqldatabase table Field Segmentation string, find the batch replacement method at the specified position

* ** Filename: TB_UpdateList. SQL * Description: sqldatabase field delimiter specified position batch replacement, reference f_split * Version: 1.0 * Created: 2014.03.12 * Author: liangjw * E-mail: liang%0

/* ===================================================== ========================================================== ** Filename: TB_UpdateList. SQL * Description: sqldatabase field delimiter to replace multiple characters at a specified position. Reference f_split * Version: 1.0 * Created: 2014.03.12 * Author: liangjw * E-mail: liang%0

/* ===================================================== ==========================================================
*
* Filename: TB_UpdateList. SQL
* Description: The sqldatabase field delimiter is replaced in batches at the specified position and f_split is referenced.
* Version: 1.0
* Created: 2014.03.12
* Author: liangjw
* E-mail: liangjw0504@163.com
* Q: 592568532
* Profile Url: http://90ideas.net/
* Company: Copyright (C) Create Family Wealth Power By Peter
*
========================================================== ================================================= */
* Remarks: uploads the encapsulation of some of the frequently-used methods summarized by yourself. There are deficiencies and imperfections. I hope you can point them out and wish to work together.
* This article mainly studies the development of erp, cms, crm, B2B, oa and other systems and websites. We welcome IT personnel who share the same pursuit and learning to learn and communicate with each other.
* Learn and discuss about asp.net mvc, Ajax, jquery, html/css, xml, sqlserver, wpf, IIS, server construction, and security technologies. <无>

/***** Object: UserDefinedFunction [dbo]. [f_split1] Script Date: 12/14/2010 21:45:01 *****/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO -- test data information -- SELECT * FROM dbo. [f_split1] ('21 | 80 | 102 | 92 | 38 | 0 | 0 | 0 | 102 | 0 | 0 | 0 ',' | ') ALTER function [dbo]. [f_split1] (@ c varchar (2000), @ split varchar (2) -- @ c represents a string, @ split represents the delimiter returns @ t table (id INT, col varchar (20) ---- returns a table that stores all split data as begin DECLARE @ tmpid I Nt = 1 while (charindex (@ split, @ c) <> 0) ---- begin insert @ t (id, col) values (@ tmpid, substring (@ c, 1, charindex (@ split, @ c)-1) ---- split a single character set @ c = stuff (@ c, 1, charindex (@ split, @ c), '') ---- SET the split string SET @ tmpid = @ tmpid + 1 end insert @ t (id, col) values (@ tmpid, @ c) ---- insert a single character to the Table @ t table return END character --------------------------------------------------------------------------------------------------------- ---------------------------------------/****** Object: StoredProcedure [dbo]. [TB_UpdateList] Script Date: 03/12/2014 17:19:15 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO -- change the data information at the specified position in the specified string. -- SELECT * FROM dbo. [f_split1] ('21 | 80 | 102 | 92 | 38 | 0 | 0 | 0 | 102 | 0 | 0 | 0 ',' | ') alter procedure [dbo]. [TB_UpdateList] AS -- determines whether the temporary table data exists. if yes, delete the temporary table if OBJECT_ID ('tempdb .. # project ') is not nullDROP TABLE # project if OBJECT_ID ('tempdb .. # projectItem ') is not nullDROP TABLE # projectItem -- obtain the data information SELECT id, SizeSum INTO # project FROM dbo. TB_MakeTrimPlan WHERE Size9> 0 -- defines the variable DECLARE @ tmpid INT, @ SizeSum NVARCHAR (500), @ tmplid INT, @ SizeLSum NVARCHAR (500) DECLARE @ SQL NVARCHAR (max ), @ listvalue NVARCHAR (500), @ getF NVARCHAR (500), @ getd NVARCHAR (500) -- CREATE a temporary table create table # projectItem (id INT, col NVARCHAR (50 )) -- Query Data Information SELECT @ tmpid = MIN (id) FROM ## projectWHILE @ tmpid is not null begin select @ SizeSum = SizeSum FROM # project WHERE id = @ tmpid insert into # projectItem SELECT * FROM dbo. [f_split1] (@ SizeSum, '|') -- drop table # projectItem SELECT @ tmplid = MIN (id) FROM # projectItem -- Define the variable DECLARE @ Ttotallistvalue NVARCHAR (500 ), @ tmpq NVARCHAR (500) SET @ Ttotallistvalue = ''set @ tmpq = ''WHILE @ tmplid is not null begin -- Query Data Information SELECT @ listvalue = col FROM # projectItem WHERE id = @ tmplid -- retrieve data information SET @ tmpq = @ tmpq + @ listvalue + '| 'if @ tmplid = 9 BEGIN -- the ninth after the split, you can customize the number of digits SET @ listvalue = 0 END -- if it is a specified position, modify the value information SET @ Ttotallistvalue = @ Ttotallistvalue + @ listvalue + '|' -- find the minimum value and then cyclically SELECT @ tmplid = MIN (id) FROM ## projectItem WHERE id> @ tmplid END -- remove the last character SET @ getF = left (@ Ttotallistvalue, len (@ Ttotallistvalue)-1) SET @ getD = left (@ tmpq, len (@ tmpq)-1) -- update data information SET @ SQL = 'Update TB_MakeTrimPlan set SizeSum = ''' + @ getF + ''' where SizeSum = ''' + @ getD + ''' EXEC (@ SQL) SELECT @ tmpid = MIN (id) FROM dbo. # project WHERE id> @ tmpid END -- determines whether the temporary table data exists. if yes, delete the temporary table if OBJECT_ID ('tempdb .. # project ') is not nullDROP TABLE # project if OBJECT_ID ('tempdb .. # projectItem ') is not nullDROP TABLE # projectItem