Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MySQL: extract data from BLOB, modify, place into another table

Posted on 2011-10-01
9
Medium Priority
?
415 Views
Last Modified: 2012-06-27
In a particular DB table, I need to locate a URL in one field, extract part of it, and copy it to another field.

The source field is (seemingly for no good reason) a MEDIUMBLOB, however it is simply html. In it is:

http://domain.com/blog/?p=XXXX

In the rows containing this URL in the field, how can I extract XXXX from the URL and copy it into another field?
0
Comment
Question by:SonicVoom
  • 4
  • 3
  • 2
9 Comments
 
LVL 24

Expert Comment

by:johanntagle
ID: 36898209
Think you're better of selecting the needed columns into a file then processing it using Perl, Sed, Awk, etc.  MySQL has regular expressions, but afaik it's only for matching, not extracting.  Do something like:

select primary_key_column, medium_blob_column from tablename
where medium_blob_column regex 'http:\/\/domain.com\/blog';

Note: obviously untested :)

0
 
LVL 23

Expert Comment

by:nemws1
ID: 36898221
From what you describe, use SUBSTR and INSTR:
UPDATE my_table
SET new_field=SUBSTR(html_field, INSTR(html_field, 'p=')+2)
;

Open in new window

0
 
LVL 24

Expert Comment

by:johanntagle
ID: 36898228
newws1 - that might work, but I think you need to chop off also any text after xxxx since I think the URL is not the only text in the column.

SonicVoom - would help if we know what comes before and after the url, i.e. is it part of an href?
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 23

Expert Comment

by:nemws1
ID: 36898547
SonicVoom didn't say there was anything after, so I assume they want everything after the 'p=' portion of the URL.  If there is more, I can provide more. ;-)
0
 
LVL 2

Author Comment

by:SonicVoom
ID: 36900374
yes, it is an html link.

<a Href =" URL " >text<a>
0
 
LVL 23

Accepted Solution

by:
nemws1 earned 2000 total points
ID: 36900439
Ah... still doable in a single SQL statement, just gets a slightly more complicated.

So, this grabs a portion from "html_field" and puts it into "new_field" (SUBSTR).  It does that by finding the first occurrence of "p=" in the field and starts looking 2 characters afterwards (to skip the "p=") - this value is stored in "@start".

Then it figures out the ending point by starting at the @start position (3rd argument to the 2nd LOCATE()) and looking for a double-quote.  However, this is an end point, and SUBSTR wants a length, so we just subtract our starting point (@start) and we have the length of part we want.
UPDATE my_table
SET new_field=SUBSTR(html_field,
                     @start:=LOCATE('p=', html_field)+2,
                     (LOCATE('"', html_field, @start) - @start)
                     )
;

Open in new window

0
 
LVL 2

Author Comment

by:SonicVoom
ID: 36906386
OK, I tried it and it works sometimes.

html_field = text and new_field=blog_ID

I failed to mention that this URL is buried in other html, and sometimes it might not exist. So sometimes it properly finds what I want, and sometimes not.

I modified the third line to include more of the actual url incase there was another p= somewhere in the database.
@start:=LOCATE('writingitreal.com/blog/?p=', `text`)+26,

It seems that when it couldn't find the search string, it just started with the 26th character of the first line.

The first line of almost every `text` field is:
<html xmlns:o="urn:schemas-microsoft-com:office:office"
so the result in many rows was:
s-microsoft-com:office:office

So I first need to make sure the string exists, and just abort if it's not present. What do I need to add?

Thanks
0
 
LVL 23

Assisted Solution

by:nemws1
nemws1 earned 2000 total points
ID: 36906849
Just add a WHERE statement:
WHERE 'text' LIKE '%writingitreal.com/blog/?p=%'

Open in new window

0
 
LVL 2

Author Closing Comment

by:SonicVoom
ID: 36907235
Thank you. It took me a few minutes where to put WHERE, but it did exactly what I needed.

final:

WHERE 'text' LIKE '%writingitreal.com/blog/?p=%'
UPDATE articles
SET `post_ID`=SUBSTR(`text`,
                     @start:=LOCATE('writingitreal.com/blog/?p=', `text`)+26,
                     (LOCATE('"', `text`, @start) - @start)
                     ) WHERE `text` LIKE '%writingitreal.com/blog/?p=%'
;
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.
Suggested Courses

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question