REPLACE Function for TEXT Column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

REPLACE Function for TEXT Column

Author

Topic

mozart
Starting Member

6 Posts

Posted - 2002-08-21 : 10:58:03

Does anyone know if there is a function similar to REPLACE for a TEXT column?

I would like to replace all occurrences of '~' with ' ', space, in a TEXT column.

Any assistance will be greatly appreciated.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-21 : 11:18:03

The only real way of doing it is to use UPDATETEXT in a loop. If you need to update more than one row you'll need a cursor too.

Check Books Online for UPDATETEXT, there's an example or two on how to use it.

mozart
Starting Member

6 Posts

Posted - 2002-08-22 : 09:52:08

robvolk,

Thank you for responding.

How can information returned by READTEXT be assigned to a local variable?

pucci70
Starting Member

20 Posts

Posted - 2002-08-22 : 10:14:56

The text, ntext, and image data types are invalid for local variables!

-----------------
Good Work!

Edited by - pucci70 on 08/22/2002 10:15:37

mozart
Starting Member

6 Posts

Posted - 2002-08-22 : 10:21:10

I would be looping through the TEXT column, checking one character at time, via offset and length, then if that character needed to be changed, UPDATEXT would be used with same offset and length.

Only one character would be returned by READTEXT not the entire TEXT column.

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-08-22 : 10:36:59

HA HA HA HA HA HA TRY THIS ...

CREATE TABLE #substring (Value varchar(32))

INSERT INTO #substring (Value)
EXEC pr_info_substring 1, 25

SELECT * FROM #substring

DROP TABLE #substring

CREATE PROCEDURE pr_info_substring (@offset INT, @length INT) AS
SET NOCOUNT ON

DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(pr_info)
FROM pub_info pr INNER JOIN publishers p
ON pr.pub_id = p.pub_id
AND p.pub_name = 'New Moon Books'
READTEXT pub_info.pr_info @ptrval @offset @length
GO

that works so you can get it back into a table at least then you can selet from the table the value...

pucci70
Starting Member

20 Posts

Posted - 2002-08-22 : 11:04:04

--Try this solution:

Use tempdb

GO

create table #t (t text)

insert into #t values
('
Hello~Hello~Hello~Hello~Hello~
Hello~Hello~Hello~Hello~Hello~
Hello~Hello~Hello~Hello~Hello~
Hello~Hello~Hello~Hello~Hello~
Hello~Hello~Hello~Hello~Hello~
Hello~Hello~Hello~Hello~Hello~
Hello~Hello~Hello~Hello~Hello~
Hello~Hello~Hello~Hello~Hello~
')

SELECT t AS 'First' FROM #t

DECLARE @in_pos INTEGER, @inter_pos INTEGER, @ptrval BINARY(16), @in_offset INTEGER

SET @inter_pos = 0
SELECT @inter_pos = COUNT(*), @in_pos = 0 FROM #t WHERE patindex('%~%', t) > 0

WHILE @inter_pos > 0 BEGIN
SET @in_pos = @in_pos + 1

SELECT @in_offset = patindex('%~%', t) - 1, @ptrval = TEXTPTR(t) FROM #t
UPDATETEXT #t.t @ptrval @in_offset 1 ' '

SELECT @inter_pos = COUNT(*) FROM #t WHERE patindex('%~%', t) > 0

END

SELECT t AS 'After' FROM #t

DROP TABLE #t

--Good Work!

================================
Thank You All For Your Help ^^
Luca

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources