If EXISTS (Select * from dbo.sysObjects where id = Object_id (n '[dbo]. [p_split]') And ObjectProperty (ID, n'isprocedure ') = 1) Drop Procedure [dbo]. [p_split] Go
/ * - NTEXT field split processing
Press the specified separator to split NTEXT field into multiple records Note: Processing process is fixed to the INT field named ID as the primary key If this condition is not satisfied, the corresponding modification stored procedure
- Zou Jian 2004.07 (Please keep this information) - * /
/ * - Call example
- test data create table tb (id int identity (1,1), content ntext) insert tb select '001,002' union all select '001,002,003,004,005,006,007,008,009,010' union all select replicate ( '001,002,003,004,005,006,007,008,009,010', 8000)
- Call the stored procedure, split EXEC P_SPLIT 'TB', 'Content', ',', 'ID = 3'
DROP TABLE TB - * /
Create Proc P_SPLIT @ TBName Sysname, - Table Name @FDName Sysname, --Text / Ntext Field Name @splitchar nvarchar (10) = ',', - Splitted String Date Division @where nvarchar (1000 ) = '' - Conditions of records to be processed Asif Isnull (@splitchar, '') = '' set @ splitchar = ','
Declare @S nvarchar (4000) set @ s = 'create table #t (id int identity (1, 1), re nvarchar (50)) Declare @ID int, @ Ptr Varbinary (16) Declare @s nvarchar (4000) , @ i int, @ j intDeclare @ sp1 varchar (10), @ Step INT
Select @ sp1 = reverse (@splitchar), @ step = len (@splitchar)
DECLARE TB CURSOR LOCAL for SELECT ID, S = SUBSTRING ([' @ fdname '], 1,4000) from [' @ TBNAME '] Where Datalength ([' @ fdname '])> 0 ' Case ISNULL @where, '') when '' Then 'Else' and (' @ where ') 'end '
Open tb fetch tb @ ID, @ SWHILE @@ fetch_status = 0begin set @ i = 1 while @S <> '' 'begin if len (@s) = 4000 select @ J = 4000-Charindex (@ SP1, Reverse (@S)), @ i = @ i @ J @ step, @ s = left (@ s, @ j) else select @ i = @ i 4000, @ j = len (@S) INSERT #T SELECT SUBSTRING (@ s, id, @ s @ splitchar, id) -id) process number table where id <= @ J @ Step and charIndex (@ splitchar, @ splitchar @ s, id) -id = 0 SELECT @ S = Substring ([' @ fdname '], @ i, 4000) from [' @ TBNAME '] where id = @ id endfetch tb @ id, @ sendclose tbdeallocate TBSELECT * from # t'Exec sp_executesql @S , N '@ splitchar nvarchar (10)', @ splitchargo
