Removing invalid characters in 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

General SQL Server Forums

New to SQL Server Programming

Removing invalid characters in column

Author

Topic

phong919
Starting Member

29 Posts

Posted - 2008-07-15 : 15:04:10

Hello all,

I was wondering if someone can show me how to removed all these invalid characters in front of the actual data.

This is what i get below:

<div>DDDDDDDDDDDDDDDDDDDEEEEEEEEEEEEEEEE</div>

HOw do i removed the "div" from both sides?

thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 15:05:58

SELECT REPLACE(REPLACE(Col1, '<div>', ''), '</div>', '')
FROM Table1

E 12°55'05.25"
N 56°04'39.16"

phong919
Starting Member

29 Posts

Posted - 2008-07-15 : 15:24:47

I get data type error in expression...

Replace(replace([ntext3], '<div>','), '</div>', '')

thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 15:29:20

Well, did you expect us to know the column datatype was NTEXT?

E 12°55'05.25"
N 56°04'39.16"

phong919
Starting Member

29 Posts

Posted - 2008-07-15 : 15:50:48

nope...sorry about that... :)

phong919
Starting Member

29 Posts

Posted - 2008-07-15 : 15:59:14

so will that syntax work?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-16 : 00:00:27

quote:
Originally posted by phong919

so will that syntax work?

Nope with ntext,text & image datatypes. You need to use UPDATETEXT function refered in this article.

IceCreamWizard
Starting Member

11 Posts

Posted - 2008-07-16 : 09:24:08

Admittedly, this method is a performance issue but it gets the job done.


select replace(replace(convert(nvarchar(max),legstat), '<div>',''), '</div>', '')
from table1

phong919
Starting Member

29 Posts

Posted - 2008-07-16 : 14:26:02

Can i just use the query for just one column?

Would it be something like - replace(column name, '<div>', ' '), '</div>', ' ')?

thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-16 : 14:29:05

quote:
Originally posted by phong919

Can i just use the query for just one column?

Would it be something like - replace(column name, '<div>', ' '), '</div>', ' ')?

thanks.

you cant use replace for text datatype. use the function specified here

http://www.sqlteam.com/article/search-and-replace-in-a-text-column

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources