SQLTeam.com | Weblogs | Forums

Edit text columns by recognizing determined text patterns


#1

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?
Martin


#2

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?


#3

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:

SELECT	[_COMMAND_] = 'SANITISE_HTML'
	, [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


#4

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.

Martin