MD5 in Oracle Sql & MySql

How to use MD5 in Oracle Sql & MySql

Sample Query Oracle Sql

SELECT LOWER( RAWTOHEX(UTL_RAW.CAST_TO_RAW(dbms_obfuscation_toolkit.md5(input_string=>”PASSWORD”)))) PASS FROM DUAL;

 

Sample Query MySql

SELECT MD5(“PASSWORD”)  AS PASS

 

OUTPUT :

PASS
319f4d26e3c536b5dd871bb2c52e3178

 

note: in am using oracle 10 above

 

FOR Update password Query Oracle Sql

UPDATE USER_TABLE SET PASSWORD = LOWER( RAWTOHEX(UTL_RAW.CAST_TO_RAW(dbms_obfuscation_toolkit.md5(input_string=>PASSWORD)))) WHERE PASSWORD IS NOT NULL;
commit;

group_concat in Mysql

How to use GROUP_CONCAT in a CONCAT in mysql

My Sample table

CREATE TABLE `mytbl` (
`id` int,
`Name` varchar(10),
`Value` int
);

INSERT INTO `mytbl` (`id`, `name`,`value`) VALUES
(1,’A’, ‘4’),
(1,’A’, ‘5’),
(1,’B’, ‘8’),
(2,’C’, ‘9’);

Group concat Example Query

select
id,group_concat(concat(`name`,’:’,`value`) separator ‘,’)
as `Column` from mytbl group by id

This MySQL statement will return unique concat string of name & values with “:” as concat string like “name:value” , as a list of strings separated by the commas Like   “name:values,name2:values2”     , order for each group of ‘id’ from the mytbl  table.

  Output 

1	A:4,A:5,B:8
2	C:9

Reference

http://sqlfiddle.com/#!2/e51b7/9

mysql function for StripTags

mysql function for StripTags ( html function ) – run it in the mysql


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 ;


Test it with this query

SELECT fnStripTags('thisis a test, nothing more');

Output:


this is a test , nothing more