List:General Discussion« Previous MessageNext Message »
From:(Hal Date:July 15 2011 12:07am
Subject:Re: How to Shuffle data
View as plain text  
>>>> 2011/07/13 19:28 +0530, Adarsh Sharma >>>>
I think a procedure can do it easily , If I know the proper function for that.
<<<<<<<<
Well, here is a procedure that copies from one table, strips off the leading
slash-separated part and reverses it by dots, and inserts the original, the reversed, and
the trailing into a temporary table:

CREATE PROCEDURE URLR()
MODIFIES SQL DATA
BEGIN
	DECLARE F, G INT;
	DECLARE R, S, T VARCHAR(199);
	DECLARE EOF BOOL DEFAULT 0;
	DECLARE X CURSOR FOR SELECT URL FROM URL;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET EOF = 1;
	CREATE TEMPORARY TABLE UU
	(	A VARCHAR(199) COMMENT 'original leading part'
	,	B VARCHAR(199) COMMENT 'dot-reversed leading part'
	,	C VARCHAR(199) COMMENT 'trailing part'
	);

	OPEN X;
	FETCH X INTO S;
	WHILE EOF = 0 DO
		SET T = SUBSTRING_INDEX(S, '/', 1);
		SET S = SUBSTRING(S, CHAR_LENGTH(T)+2);
		SET G = 1, F = LOCATE('.', T), R = NULL;
		WHILE F > 0 DO
			SET R = CONCAT_WS('.', SUBSTRING(T, G, F-G), R);
			SET G = F+1;
			SET F = LOCATE('.', T, G);
		END WHILE;
		SET R = CONCAT_WS('.', SUBSTRING(T, G), R);
		INSERT INTO UU VALUE (T, R, S);
		FETCH X INTO S;
	END WHILE;
	CLOSE X;
END

It seemed to me that in your examples you only reversed the domain name around its
dot-separated words, and that this does.

Thread
How to Shuffle dataAdarsh Sharma13 Jul
  • Re: How to Shuffle dataReindl Harald13 Jul
  • Re: How to Shuffle datawalter13 Jul
    • Re: How to Shuffle dataAdarsh Sharma13 Jul
      • RE: How to Shuffle dataJerry Schwartz14 Jul
        • Re: How to Shuffle datashawn wilson14 Jul
      • Re: How to Shuffle datahsv15 Jul
  • Re: How to Shuffle datahsv13 Jul
  • Re: How to Shuffle datashawn wilson14 Jul
  • Re: How to Shuffle dataRaj Shekhar14 Jul