/* sample data -- testing SELECT fn_efgetvalue( "rvrsuivi", efd.form_data ) AS Value FROM eform_data efd WHERE fdid = 21 ; SELECT fn_efgetvalue( "rvr8875", efd.form_data ) AS Value FROM eform_data efd WHERE fdid = 21 ; SELECT fn_efgetvalue( "hin", efd.form_data ) AS Value FROM eform_data efd WHERE fdid = 21 ; */ SET GLOBAL log_bin_trust_function_creators=1; DROP FUNCTION IF EXISTS fn_efgetvalue; DELIMITER | CREATE FUNCTION fn_efgetvalue( FindStr varchar(20), Dirty mediumtext ) RETURNS varchar(4000) DETERMINISTIC BEGIN DECLARE iPNam, iPVal, iPEnd int; SET iPNam = LOCATE( CONCAT('name="',FindStr,'"'), Dirty ); IF iPNam > 0 THEN SET iPNam = iPNam + 6 + LENGTH(FindStr) ; SET iPEnd = LOCATE( '>', MID( Dirty, iPNam ) ); SET iPVal = LOCATE( 'value="', MID( Dirty, iPNam, iPEnd ) ) ; IF iPVal > 0 THEN SET Dirty = MID( Dirty, iPNam + iPVal + 6 , iPEnd ); SET Dirty = LEFT( Dirty, LOCATE( '"', Dirty ) -1 ); ELSE SET Dirty = "" ; END IF; ELSE SET Dirty = "null" ; END IF; RETURN Dirty; END; | DELIMITER ;