Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: Enterprise
Expertise: Intermediate
Jul 3, 2001



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Manipulating Text Field Data in a SQL Stored Procedure

The code below is functionally equivalent to a VB Replace function, but, instead, it allows you to perform an action in a Text type field in a SQL Server Stored Procedure. This functionality can be especially useful in content management type situations, allowing you, for example, to replace all carriage returns with an HTML
CREATE Procedure sp_replace
DECLARE	        @pointer binary(16),
		@iPatternLoc Integer,
		@rowId Integer
--@rowId is an arbitrary row identifier this could be passed in by
-- a parameter

SELECT @rowId = 1

--Select Satement below retrieves first location of a searched
--pattern (in this case carriege return) and assigns it to
--@iPatternLoc variable
SELECT @iPatternLoc = PATINDEX('%' + CHAR(13) + '%',text_field)
FROM someTable WHERE someTableId = @rowId

--This Select statement returns a pointer variable pointed to the
SELECT @pointer = TEXTPTR(text_field)
FROM someTable
WHERE text_field = @rowId

Select @iPatternLoc = @iPatternLoc - 1
--Loop through all instances of Char(13) and replace them with
using the UPDATETEXT function WHILE @iPatternLoc > -1 BEGIN UPDATETEXT text_field @pointer @iPatternLoc 4 '
' SELECT @iPatternLoc = PATINDEX('%' + CHAR(13) + '%',text_field) FROM someTable WHERE someTableId = @rowId Select @iPatternLoc = @iPatternLoc - 1 END
Martin Gruszka
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date