Replace Function - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Replace Function

Author

Topic

ZMike
Posting Yak Master

110 Posts

Posted - 2010-01-13 : 10:09:12

I'm pulling data from Microsoft Sharepoint on SQL Server. I'm Pulling a multi colum field and in SQL Server it will show just in one field which is fine. I want to do a replace of the return commands '<div>' OR '</div>'

I would like to do something like this

REPLACE (dbo.UserData.ntext2,'<div>' OR '%</div>%' ,'') AS Comments

Any Suggestions ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 10:25:28

you need to do it in steps

REPLACE(REPLACE (dbo.UserData.ntext2,'<div>' ,''),'</div>','')

ZMike
Posting Yak Master

110 Posts

Posted - 2010-01-13 : 10:42:57

visakh16

Thanks for the advice..... I get the following error.

Msg 8116, Level 16, State 1, Line 1
Argument data type ntext is invalid for argument 1 of replace function.

IT looks like it doesnt like the field that I'm searching dbo.UserData.ntext2

I just looked up that field and it's a NText which isnt a field I'm used to using... Any ideas ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 10:44:49

quote:
Originally posted by ZMike

visakh16

Thanks for the advice..... I get the following error.

Msg 8116, Level 16, State 1, Line 1
Argument data type ntext is invalid for argument 1 of replace function.

IT looks like it doesnt like the field that I'm searching dbo.UserData.ntext2

I just looked up that field and it's a NText which isnt a field I'm used to using... Any ideas ?

then cast to varchar(max) and then replace

ZMike
Posting Yak Master

110 Posts

Posted - 2010-01-13 : 12:56:11

Thanks Visakh16

REPLACE(REPLACE(CAST(dbo.UserData.ntext2 AS VARCHAR), '<div>', ''), '</div>', '') AS Comments

That worked for me.

Thank you very much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 12:57:17

welcome

SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources