--Test Data
CREATE TABLE TB (Col ntext)
INSERT TB VALUES (Replicate ('0001,0002,0003,0004,000, 0100, "
'220000001, 302000004, 500200006, 700002008, 900002120,', 800))
Declare @P binary (16)
SELECT @ p = textptr (color from TB
Updatetext tb.col @P null 0 tb.col @P
Go
- Replacement processing definition declare @s_str nvarchar (1000), @ r_str nvarchar (1000) SELECT @ S_STR = '00 '- To replace the string, @ r_str =' 0000 '- replace it with this string
Declare @P Varbinary (16) Declare @start int, @ s nvarchar (4000), @ len intdeclare @s_len int, @ step int, @ last_repl int, @ Pos int, @ Pos INT
- Replacement processing parameter settings SELECT - Used to determine that each intercept data, the last process of replacing the data position @S_LEN = LEN (@S_STR),
- Set the length of the data that should be intercepted each time, prevent the replace after the data overflow @ Step = case when len (@r_str) "LEN (@s_str) THEN 4000 / LEN (@S_STR) ELSE 4000 END
- Replacement processing start position Select @ start = Patindex ('%' @ s_str '%', col), @ p = textptr (color), @ s = substring (col, @ Start, @ Step), @LEN = LEN (@S), @ last_repl = 0from Tbwhere Patindex ('%' @ s_str '%', col)> 0 and textValid ('tb.col', textptr (color) = 1WHILE @LEN> = @ S_Lenbegin - Get the last position of the last replacement data while charindex (@ s_str, @ s, @ last_repl)> 0 set @ last_repl = @ s_len charindex (@ s_str, @ s, @ Last_Repl)
- If you need, update the data, and determine the offset IF @ Last_Repl = 0 set @ Last_Repl = 0 set @ Last_Repl = 0 set @ Last_Repl = 0 set @ Last_REPL = 0 set @ Last_REPL = @ Last_REPL = Case When @LAST_REPL THEN 1 by @ @ @ @ @ Last_repl> = @ S_LEN THEN @S_LEN Else @ len- @ Last_Repl 2 End, @ s @ s, @ s_str, @ r_str), @ POS = @ Start-1 UpdateText Tb.col @POS @LEN @S End - Get the next data to be processed SELECT @ start = @ Start LEN (@S) - @ Last_REPL 1, @ s = Substring (Col, @ Start, @ Step), @ len = LEN (@ s), @ last_repl = 0 from Tbendgo - Display Process Select Datalength (Col), * from TB
