Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT * from table WHERE colmn_name ` like '%mytext%' "
- SET GLOBAL log_bin_trust_function_creators=1;
- DROP FUNCTION IF EXISTS fnStripTags;
- DELIMITER |
- CREATE FUNCTION fnStripTags( Dirty varchar(4000) )
- RETURNS varchar(4000)
- DETERMINISTIC
- BEGIN
- DECLARE iStart, iEnd, iLength int;
- WHILE Locate( '<', Dirty ) > 0 And Locate( '>', Dirty, Locate( '<', Dirty )) > 0 DO
- BEGIN
- SET iStart = Locate( '<', Dirty ), iEnd = Locate( '>', Dirty, Locate('<', Dirty ));
- SET iLength = ( iEnd - iStart) + 1;
- IF iLength > 0 THEN
- BEGIN
- SET Dirty = Insert( Dirty, iStart, iLength, '');
- END;
- END IF;
- END;
- END WHILE;
- RETURN Dirty;
- END;
- |
- DELIMITER ;
- SELECT fnStripTags('this <html>is <b>a test</b>, nothing more</html>');
- CREATE FUNCTION `strip_tags`($str text) RETURNS text
- BEGIN
- DECLARE $start, $end INT DEFAULT 1;
- LOOP
- SET $start = LOCATE("<", $str, $start);
- IF (!$start) THEN RETURN $str; END IF;
- SET $end = LOCATE(">", $str, $start);
- IF (!$end) THEN SET $end = $start; END IF;
- SET $str = INSERT($str, $start, $end - $start + 1, "");
- END LOOP;
- END;
- mysql> select strip_tags('<span>hel<b>lo <a href="world">wo<>rld</a> <<x>again<.');
- +----------------------------------------------------------------------+
- | strip_tags('<span>hel<b>lo <a href="world">wo<>rld</a> <<x>again<.') |
- +----------------------------------------------------------------------+
- | hello world again. |
- +----------------------------------------------------------------------+
- 1 row in set
- SELECT * from table WHERE colmn_name REGEXP '>[^<]*mytext';
- set Dirty = Replace(Dirty,' ',''); #No space between & and nbsp;
- set Dirty = Replace(Dirty,'r','');
- set Dirty = Replace(Dirty,'n','');
- ...BEGIN
- DECLARE $start, $end INT DEFAULT 1;
- SET $str = REPLACE($str, " ", " ");
- SET $str = REPLACE($str, "€", "€");
- SET $str = REPLACE($str, "á", "á");
- SET $str = REPLACE($str, "é", "é");
- SET $str = REPLACE($str, "í", "í");
- SET $str = REPLACE($str, "ó", "ó");
- SET $str = REPLACE($str, "ú", "ú");
- LOOP...
- # MySQL function to programmatically replace out specified html tags from text/html fields
- # run this to drop/update the stored function
- DROP FUNCTION IF EXISTS `strip_tags`;
- DELIMITER |
- # function to nuke all opening and closing tags of type specified in argument 2
- CREATE FUNCTION `strip_tags`($str text, $tag text) RETURNS text
- BEGIN
- DECLARE $start, $end INT DEFAULT 1;
- SET $str = COALESCE($str, '');
- LOOP
- SET $start = LOCATE(CONCAT('<', $tag), $str, $start);
- IF (!$start) THEN RETURN $str; END IF;
- SET $end = LOCATE('>', $str, $start);
- IF (!$end) THEN SET $end = $start; END IF;
- SET $str = INSERT($str, $start, $end - $start + 1, '');
- SET $str = REPLACE($str, CONCAT('</', $tag, '>'), '');
- END LOOP;
- END;
- | DELIMITER ;
- # test select to nuke all opening <a> tags
- SELECT
- STRIP_TAGS(description, 'a') AS stripped
- FROM
- tmpcat;
- # run update query to replace out all <a> tags
- UPDATE tmpcat
- SET
- description = STRIP_TAGS(description, 'a');
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement