Replace data portion of a column

Hi all. I have a table, with information that complies with a condition
similar to this:
Select
*
From dbo.Object
Where
Name Like '%triplle%'
And I need to replace, for all those rows, in the column [Name], the word
'Triplle' with 'Triple', and leave the rest of the characters in [Name],
intact.
For example:
'This is an example triplle' convert to 'This is an example triple'
'Another triplle here' convert to 'Another triple here'
'and triplle here also' convert to 'and triple here also'
Problem is, that [Name] is of type NText, and Replace doesn't work with
NText. So far, I'm using this, which would be a problem, if the text
contained in [Name], is greater than 3000 (or any other number that I use):
Update dbo.Object
Set
Name = Replace( Convert( NChar( 3000 ), Valor ), 'triplle', 'triple' )
Where
Name Like '%triplle%'
I came up with another solution, which looks too complicated (but it works),
by using TextPtr, CharIndex, UpDateText. I have the idea that there should
be a simpler and better solution. Is there such?
Appreciate your help,
FrankCREATE TABLE testb ([name] ntext)
INSERT INTO testb VALUES ('This is an example triplle')
INSERT INTO testb VALUES ('Another triplle here')
INSERT INTO testb VALUES ('and triplle here also')
select substring([name], 0, PATINDEX('%triplle%',[name])) + 'triple' +
substring([name], PATINDEX('%triplle%',[name]) + 7,DATALENGTH([name]))
from testb
This will work if the word 'triplle' occurs only once for each value of the
ntext column. If it could appear multiple times, then encapsulate the above
within a WHILE loop to remove all occurrences.
--
If you posted to this forum through TechNet, and you found my answers
helpful, please mark them as answers.
"John Francisco Williams" wrote:

> Hi all. I have a table, with information that complies with a condition
> similar to this:
> Select
> *
> From dbo.Object
> Where
> Name Like '%triplle%'
> And I need to replace, for all those rows, in the column [Name], the word
> 'Triplle' with 'Triple', and leave the rest of the characters in [Name],
> intact.
> For example:
> 'This is an example triplle' convert to 'This is an example triple'
> 'Another triplle here' convert to 'Another triple here'
> 'and triplle here also' convert to 'and triple here also'
> Problem is, that [Name] is of type NText, and Replace doesn't work with
> NText. So far, I'm using this, which would be a problem, if the text
> contained in [Name], is greater than 3000 (or any other number that I use):
> Update dbo.Object
> Set
> Name = Replace( Convert( NChar( 3000 ), Valor ), 'triplle', 'triple'
)
> Where
> Name Like '%triplle%'
> I came up with another solution, which looks too complicated (but it works
),
> by using TextPtr, CharIndex, UpDateText. I have the idea that there should
> be a simpler and better solution. Is there such?
> Appreciate your help,
> Frank
>
>