SQLTeam.com | Weblogs | Forums

Edit text columns by recognizing determined text patterns


I have to eliminate and edit some tags from a lot of text files. My idea was to process them as text columns(max) through SQL. There are 3 operations I need to do:

  1. a simple Replace operation of a string that always is the same (no need required)

  2. a Replace Operation of a String that includes a GUID String that varies

    .htm') } else { document.location='a5b3345e-2fcf-4843-b656-4210fb76f075.htm' }"
    .htm') } else { document.location='2924e8be-f9fb-44b7-8e49-e6cb0f0f39f5.htm' }"

  3. Delete all defined onmousemove/out TAGs - the parameters do vary all the time

    onmousemove="javascript:onObjectMouseOver('shape5-23', '', 'shape445-1463', evt);" onmouseout="javascript:onObjectMouseOut('shape5-23', '', 'shape445-1463');"

Any comment on this?


OK -- this looks like javascript code.

Where does SQL come into this?

Is this code stored in a database table? If so, would you please post the table definition?


Very hard to do in SQL IME. The GUID ought to be OK, but still the opportunity for something inappropriate to be matched - this type of Find &Replace is so "out of sight, out of mind" that things go wrong and no-one spots that it has happened for ages ...

The 3rd one is hard because of the likihood of nested - parenthesis in this case, but similar thing with any HTML / XML tags.

Doing this in a language that provides a suitable parser is a much more sure-fire bet IME.

Bit of a longshot but:

We do do this with some mangling of HTML from SQL. We have a stored procedure, called from the APP, and the SProc needs to sanitise the HTML in a varchar(MAX) @Variable at some point.

We have a single, central, "Process next resultset" function in our APP. That looks at the first column and if its name is "_COMMAND_" then it processes the resultset itself, and passes the next resultset (if any) back to the APP instead.

Thus we can do:

	, [HTML_TEXT] = @MyVariable
	, [CALLBACK_SPROC] = 'dbo.MyCallbackSprocName'
	, [PKeyValue] = @SomeID

in this one I want the Resulset Processing function to use the "SANITISE_HTML" function, that require an HMTL_TEXT colum and a CALLBACK_SPROC column with the name of an Sproc to then EXEC. That EXEC will also pass back the PKeyValue - so the Sproc knows where to store the result.

Our APP is web-based so has ready access to HTML Parsing functions


Ok. I think I give up before even having really started. I haven't set up the table yet and was trying to achieve this with working with @variables. But the first obstacle with all these quotes and double-quotes that mess up everything was already too hard. It's all about a database on processes that are depicted in a svg file with hyperlinks that have changed. In the database I have the correct guid that lead to the correct id. In some smart way I need to edit the svg files, so that the links work.