SHARE
TWEET

Untitled

a guest Jul 17th, 2017 54 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT * from table  WHERE colmn_name ` like '%mytext%' "
  2.    
  3. SET GLOBAL log_bin_trust_function_creators=1;
  4. DROP FUNCTION IF EXISTS fnStripTags;
  5. DELIMITER |
  6. CREATE FUNCTION fnStripTags( Dirty varchar(4000) )
  7. RETURNS varchar(4000)
  8. DETERMINISTIC
  9. BEGIN
  10.   DECLARE iStart, iEnd, iLength int;
  11.     WHILE Locate( '<', Dirty ) > 0 And Locate( '>', Dirty, Locate( '<', Dirty )) > 0 DO
  12.       BEGIN
  13.         SET iStart = Locate( '<', Dirty ), iEnd = Locate( '>', Dirty, Locate('<', Dirty ));
  14.         SET iLength = ( iEnd - iStart) + 1;
  15.         IF iLength > 0 THEN
  16.           BEGIN
  17.             SET Dirty = Insert( Dirty, iStart, iLength, '');
  18.           END;
  19.         END IF;
  20.       END;
  21.     END WHILE;
  22.     RETURN Dirty;
  23. END;
  24. |
  25. DELIMITER ;
  26. SELECT fnStripTags('this <html>is <b>a test</b>, nothing more</html>');
  27.    
  28. CREATE FUNCTION `strip_tags`($str text) RETURNS text
  29. BEGIN
  30.     DECLARE $start, $end INT DEFAULT 1;
  31.     LOOP
  32.         SET $start = LOCATE("<", $str, $start);
  33.         IF (!$start) THEN RETURN $str; END IF;
  34.         SET $end = LOCATE(">", $str, $start);
  35.         IF (!$end) THEN SET $end = $start; END IF;
  36.         SET $str = INSERT($str, $start, $end - $start + 1, "");
  37.     END LOOP;
  38. END;
  39.    
  40. mysql> select strip_tags('<span>hel<b>lo <a href="world">wo<>rld</a> <<x>again<.');
  41. +----------------------------------------------------------------------+
  42. | strip_tags('<span>hel<b>lo <a href="world">wo<>rld</a> <<x>again<.') |
  43. +----------------------------------------------------------------------+
  44. | hello world again.                                                   |
  45. +----------------------------------------------------------------------+
  46. 1 row in set
  47.    
  48. SELECT * from table  WHERE colmn_name REGEXP  '>[^<]*mytext';
  49.    
  50. set Dirty = Replace(Dirty,'&nbsp;',''); #No space between & and nbsp;
  51. set Dirty = Replace(Dirty,'r','');
  52. set Dirty = Replace(Dirty,'n','');
  53.    
  54. ...BEGIN
  55.     DECLARE $start, $end INT DEFAULT 1;
  56.     SET $str = REPLACE($str, "&nbsp;", " ");
  57.     SET $str = REPLACE($str, "&euro;", "€");
  58.     SET $str = REPLACE($str, "&aacute;", "á");
  59.     SET $str = REPLACE($str, "&eacute;", "é");
  60.     SET $str = REPLACE($str, "&iacute;", "í");
  61.     SET $str = REPLACE($str, "&oacute;", "ó");
  62.     SET $str = REPLACE($str, "&uacute;", "ú");
  63. LOOP...
  64.    
  65. # MySQL function to programmatically replace out specified html tags from text/html fields
  66.  
  67. # run this to drop/update the stored function
  68. DROP FUNCTION IF EXISTS `strip_tags`;
  69.  
  70. DELIMITER |
  71.  
  72. # function to nuke all opening and closing tags of type specified in argument 2
  73. CREATE FUNCTION `strip_tags`($str text, $tag text) RETURNS text
  74. BEGIN
  75.     DECLARE $start, $end INT DEFAULT 1;
  76.     SET $str = COALESCE($str, '');
  77.     LOOP
  78.         SET $start = LOCATE(CONCAT('<', $tag), $str, $start);
  79.         IF (!$start) THEN RETURN $str; END IF;
  80.         SET $end = LOCATE('>', $str, $start);
  81.         IF (!$end) THEN SET $end = $start; END IF;
  82.         SET $str = INSERT($str, $start, $end - $start + 1, '');
  83.         SET $str = REPLACE($str, CONCAT('</', $tag, '>'), '');
  84.     END LOOP;
  85. END;
  86.  
  87. | DELIMITER ;
  88.  
  89. # test select to nuke all opening <a> tags
  90. SELECT
  91.     STRIP_TAGS(description, 'a') AS stripped
  92. FROM
  93.     tmpcat;
  94.  
  95. # run update query to replace out all <a> tags
  96. UPDATE tmpcat
  97. SET
  98.     description = STRIP_TAGS(description, 'a');
RAW Paste Data
Top