Split table data in SQL and replace with results_问答_开发者

Split table data in SQL and replace with results

开发者 https://www.devze.com 2023-02-07 09:56 出处：网络

I need to remove a bunch of unneeded data from each table based on split parameters. My SQL table is storing a bunch of HTML for caching, The data is already in SQL and it\'s growing to be quite larg

I need to remove a bunch of unneeded data from each table based on split parameters.

My SQL table is storing a bunch of HTML for caching, The data is already in SQL and it's growing to be quite large so now I want to split some of the data I don't use from each table based on a string and update the table with the new results.

cacheHTML table is holding data like this

<html>
... (a bunch of data I don't need)
<first div>
... (the data I do want to save)
</div>
... (data I don't care about also)
</html>

I only want whats inside the first div and to remove all the html up to that point.

Is there any easy method for this? I need to do this to 5k rows of cached data...

I need a function or method to say give me everything between string1 till string2 then replace the tab开发者_如何学Cle with the results. Any help would be appreciated Thanks!

You could do something like this. Will only work if you always need the text inside the first div in the html string. Im assuming SQL Server as database system but it could probably be translated to others pretty easily.

Sample html string:

<html>
<head>
    <title>Stuff i dont need</title>
</head>
<body>
    <h1>Stuff i dont need</title>
    <p>I dont need any of this data</title>
    <div>This is the data i need to save!</div>
    <h3>Dont need this</h3>
    <div>Wont need this either!<div>
    <h3>Bye</h3>
</body>

SQL to do the update:

UPDATE cacheHTML
SET htmlText = REPLACE(SUBSTRING(htmlText, CHARINDEX('<div>', htmlText, 0), CHARINDEX('</div>', htmlText, 0) - CHARINDEX('<div>', htmlText, 0)), '<div>', '')