>>>> 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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
