1

I'm looking for an SQL query that will delete part of a field leaving the rest of the information intact.

I'm looking to change:

Any text <div style="display:none">HACKED CODE WITH DIFFERENT URLS</div> and any text.

To:

Any text and any text.

I've tried:

update TABLENAME
set COLUMN = replace(COLUMN,'<div%div>','')

but realise now that "replace" cannot use wildcards.

Morgan Thrapp
  • 9,748
  • 3
  • 46
  • 67
MHVL
  • 21
  • 3
  • Is there a replace_regexp in SQL Server? – jarlh Oct 20 '15 at 14:19
  • 2
    This looks helpful: http://stackoverflow.com/questions/21378193/regex-pattern-inside-sql-replace-function – David Oct 20 '15 at 14:21
  • 2
    Maybe it would be easier to do this outside the database? For example dump the tables into files with bcp, fix the data and load it back in? – James Z Oct 20 '15 at 14:24
  • Thanks, I'll take a look at that, but I'm a designer not a developer and it looks way above my head! – MHVL Oct 20 '15 at 14:26
  • I would use something outside of sql to process the information. Like PHP? – Jamesking56 Oct 20 '15 at 14:36
  • @Jamesking56 In a table with tens of thousands of rows, reading the data, modifying it all and then updating each row back in the table can take a very long time. So, I wouldn't recommend this "ping-pong" between an application and the database. – Radu Gheorghiu Oct 20 '15 at 14:52

1 Answers1

1

I think this should work for you. You just need a little more complex condition to filter/remove the data based on the pattern you found.

UPDATE TableName
SET COLUMN = LEFT(COLUMN, CHARINDEX('<', COLUMN)-1) +
             RIGHT(COLUMN, LEN(COLUMN)-CHARINDEX('</div>', COLUMN)-6)
WHERE CHARINDEX('</div>', COLUMN) > 0

Here is a sample SQLFiddle with how the code works on the sample text you provided above.

Or, in case SQLFiddle doesn't work, here is the code:

DECLARE @var NVARCHAR(max)
SET @var = 'Any text <div style="display:none">HACKED CODE WITH DIFFERENT URLS</div> and any text.'

SELECT 
    LEFT(@var, CHARINDEX('<', @var) - 1) + 
    RIGHT(@var, LEN(@var) - CHARINDEX('</div>', @var) - 6)

Update:

I have updated my answer with a solution to modify only those records which have this pattern.

Radu Gheorghiu
  • 20,049
  • 16
  • 72
  • 107
  • Thanks Radu, I'm getting the following error though `Invalid length parameter passed to the RIGHT function` any ideas? – MHVL Oct 20 '15 at 15:15
  • I've been playing about with the code and for some reason it works when I drop the length parameter to -5 and use `where COLUMN like '%<%>%'`. I think it may be due to the hacked code being anywhere - either in the middle of the string, at the beginning or at the end, but don' hold me to that! Thank you so much again Radu, you're a life saver! – MHVL Oct 21 '15 at 11:34
  • @MHVL Somehow I missed your previous notification. Yes, the error you were getting was because some rows did not fall into that pattern, but givee a couple of minutes and I can give you a solution for that error. – Radu Gheorghiu Oct 21 '15 at 11:37
  • @MHVL I've modified my query so that you don't get this error anymore. The `UPDATE` will only work on those rows where this pattern is respected. Please try this updated version. – Radu Gheorghiu Oct 21 '15 at 13:54
  • Thanks again Radu! That works, but I still had to reduce the RIGHT length to -5 for some reason. Thank you once again. – MHVL Oct 21 '15 at 14:25