Support for Text fields only: The following functions and statements can be used with NTEXT, TEXT, or IMAGE data. Function statement Datalength Readtext Patindex Set Textsize Substring UpdateText Textptr WriteText TextValid
1: Replace
- Create a data test environment Create Table #TB (aa text) Insert INTO #TB SELECT 'ABC123ABC123, ASD'
- Define a string declare @s_str varchar (8000), @ D_Str = '123' - To replace the string, @ D_STR = '000' - string replacement
- String replacement processing declare @P var, @ rplen intSelect @ p = textptr (aa), @ rplen = len (@S_STR), @ postion = charIndex (@ s_str, aa) -1 From #tbwhile @POSTION> 0BEGIN UPDATEXT # TB.AA @P @POSTION @rplen @d_str select @ postion = charindex (@ s_str, aa) -1 from #tbend
- Display result Select * from #TB
- Delete Data Test Environment DROP TABLE #TB
/ *************** All replace *********************************** / DECLARE @PTRVAL BINARY (16 ) Select @ptrval = textptr (aa) from #tb where aa like '% Data 2%' if @ptrval is not null - Be sure to add this sentence, otherwise, if the data is not found, it will report an error updateText #TB. .aa @ptrval 0 null 'data 3'
/ ****************************************************************************************************************************** ***** / - Define Added String Declare @S_Str VARCHAR (8000) SELECT @S_STR = '* C' - String to add DECLARE @P Varbinary (16), @POSTION INT, @ rplen intSelect @ p = textptr (detail) from test where id = '001'Updatetext test.detail @P null null @s_str
Summary: 1: Text field type cannot be replaced with the Replace function directly, you must use the UpdateText 2: Field Compare cannot with WHERE field = 'a data', you can use Like instead of 3: UpdateText, if @ptrval value is empty error Need to pay attention.
