SQL text search loop

Unknown UNKNOWN
January 16, 2009 at 06:44:29
Specs: Windows XP, unknown / 512MB
Hi,

i'm using mysql 5.0.27. There is a table named contents. It has a column named body. I would like to extract all URL from column body. There might be none or several of them. The column body has the datatype text. How could that be done?


See More: SQL text search loop

Report •


#1
January 18, 2009 at 07:34:18
You don't indicate if the urls you are seeking are explicit website addresses or fragments of html etc. If there are multiple urls within a given record, you will have to parse (search, edit/extract) the text string and break it into individual urls.

You can select only those records that have some text in the body column.
SELECT BODY from CONTENTS
WHERE BODY is NOT NULL.

You can select only those records that have urls (depending on the format of such urls) Will your data have the http:// or not?

SELECT BODY from CONTENTS
WHERE BODY like ("http://*")

Here the * represents any number of any char.
Check the proper character for this in MySql.

You did not indicate if multiple urls are separated by comma, space, tab etc.
So you will have to manipulate any results to separate out individual urls.
Hope this is helpful.


Report •

#2
January 19, 2009 at 09:00:29
yeah, see above post but in mysql you need to use the % character rather than the asterisk.

Report •

#3
January 23, 2009 at 16:48:41
Thanks for the responses. The column body contains multiple "http://a.php<br />" strings. I would like to insert all of them from all rows into another table. Each of them should go into it's own row.

<-- body := "http://a.php-del- test test http://b.php-del-"

-del- would be "lt br space slash gt"

another table should be filled with:
--> row 1: "http://a.php"
--> row 2: "http://b.php"

How could they be all fetched and written?


Report •

Related Solutions


Ask Question