Page 1 of 1

A question regarding LIKE

Posted: Fri Dec 10, 2004 6:53 pm
by josh
I have a line of information I want to delete from a BLOB column on 4,000 records, I can easily pull up all 4,000 columns where the line i want to get rid of is by doing

Code: Select all

SELECT `data` FROM `table` WHERE `column` LIKE '%line%'
but how can I write an sql query to delete that line? I don't want to delete that record, just that line, also the line isnt always the same line #

One file might be like

line 1
line 4
line 2
line 3

Another file might be like
line 4
line 2

How would I do something like DELETE '%line 4%' from `column` ya know?
(Not all of them have the same amount of lines)

Posted: Fri Dec 10, 2004 7:24 pm
by timvw
ah, the mysql manual is one of my favorite bookmarks....


REPLACE(str,from_str,to_str)
Returns the string str with all occurrences of the string from_str replaced by the string to_str.

mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
-> 'WwWwWw.mysql.com'

This function is multi-byte safe.

Posted: Fri Dec 10, 2004 7:45 pm
by josh
Ok but can str have wildcards?
the line I am deleteing is not always the same for all records

Posted: Sun Dec 12, 2004 9:44 am
by josh
REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record for a PRIMARY KEY or a UNIQUE index, the old record is deleted before the new record is inserted. See section 13.1.4 INSERT Syntax.

Note that unless the table has a PRIMARY KEY or UNIQUE index, using a REPLACE statement makes no sense. It becomes equivalent to INSERT, because there is no index to be used to determine whether a new row duplicates another.
That's not what I wanted.. here I'll give you the php code for what I am doing

Code: Select all

<?php
$system=secure($_GET['system']);
$result = mysql_query("SELECT `filename`, `title`, `html` FROM `$system`;") or die(mysql_error());
while(list($filename, $title, $html) = mysql_fetch_array($result)) {
	$newfile=preg_replace("/$system\/(.*)\.php/", "\\1", $filename);
	if ($newfile==$title) {
		echo $title . '=>';
		$newtitle=preg_replace("/(.*)<h1 align="center">(.*?)<\/h1>(.*)/", "\\2", $html);	
		$newtitle=nl2br($newtitle);
		$newtitle=str_replace("<BR>", "", $newtitle);
		if (strlen($newtitle)<75) {
			echo $newtitle . '<BR>';
			dbq("UPDATE `$system` SET `title` = '" . secure($newtitle) . "' WHERE `filename`= '$filename' ; ");
		} else {
			echo 'un_changed' . '<BR>';
		}
	}
}
?>
Is there a way to avoid so many calls to the database (besides loading one huge query into a string and running it all at once), I want to know basically is there a function that could be run on a large amount of records that does what I am doing, but without makeing the php script take forever to execute.

Posted: Sun Dec 12, 2004 9:54 am
by timvw
actually, i was talking about the string function replace... (not the data manipulation thingie you found)

http://dev.mysql.com/doc/mysql/en/String_functions.html


but i'm to tired right now to think about a query to do all what you want to do ;)