MySQL: Copy tables (Only structure and NO DATA) using stored procedure

Last week some one asked how to Copy the tables (Only structure and NO DATA..also no other DB objects) from one schema to another on EE. This can be easily done from command line but user wanted to do this thru stored procedure.
http://www.experts-exchange.com/Database/MySQL/Q_25024073.html
So I came with a very small MySQL procedure which was doing as needed by the user. I'm not sure whether this is the best way to do this but "There is always room for improvement."
 
DELIMITER $$

DROP PROCEDURE IF EXISTS `CopySchema`$$
CREATE PROCEDURE `CopySchema`(sourceSchema VARCHAR(64),targetSchema VARCHAR(64))


BEGIN

DECLARE no_more_rows BOOLEAN;
DECLARE loop_cntr INT DEFAULT 0;
DECLARE num_rows INT DEFAULT 0;
DECLARE my_table VARCHAR(64);

DECLARE my_cur CURSOR FOR
SELECT TABLE_NAME AS myTable
 FROM information_schema.TABLES
WHERE information_schema.TABLES.TABLE_SCHEMA=sourceSchema AND TABLE_TYPE = 'BASE TABLE';


DECLARE CONTINUE HANDLER FOR NOT FOUND


SET no_more_rows = TRUE;
SET @tmp_sql= CONCAT("CREATE DATABASE IF NOT EXISTS ",targetSchema);
PREPARE s1 FROM @tmp_sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;


OPEN my_cur;
select FOUND_ROWS() into num_rows;

the_loop: LOOP
    FETCH my_cur
      INTO my_table;

     IF no_more_rows THEN
         CLOSE my_cur;
         LEAVE the_loop;
     END IF;


     SET @tmp_sql= CONCAT("CREATE TABLE IF NOT EXISTS ",targetSchema,'.',my_table,' LIKE ',sourceSchema,'.',my_table);
      PREPARE s1 FROM @tmp_sql;
      EXECUTE s1;
     DEALLOCATE PREPARE s1;

     SET loop_cntr = loop_cntr + 1;
END LOOP the_loop;

SELECT CONCAT('Summary: ', loop_cntr, ' tables copied from schema "',sourceSchema,'" to "',targetSchema,'"') AS "Schema copying";


END$$
DELIMITER ;

Usage:

call CopySchema('dba','dba_dummy');

MySQL - Extract numbers out of a string

Yesterday on EE I saw a very interesting request from a user for "Extracting numbers out of a string".
This could be done in other languages with just 1 liner code but he needed it inside a SELECT query.
 http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_24855357.html

So I came with a very small MySQL function which was doing as needed by the user. I'm not sure whther this is the best way to do this but "There is always room for improvement."
 
 
DELIMITER $$



DROP FUNCTION IF EXISTS `uExtractNumberFromString`$$
CREATE FUNCTION `uExtractNumberFromString`(in_string varchar(50)) RETURNS INT
NO SQL


BEGIN


DECLARE ctrNumber varchar(50);
DECLARE finNumber varchar(50) default ' ';
DECLARE sChar varchar(2);
DECLARE inti INTEGER default 1;


IF length(in_string) > 0 THEN


WHILE(inti <= length(in_string)) DO
    SET sChar= SUBSTRING(in_string,inti,1);
    SET ctrNumber= FIND_IN_SET(sChar,'0,1,2,3,4,5,6,7,8,9');


    IF ctrNumber > 0 THEN


       SET finNumber=CONCAT(finNumber,sChar);
    ELSE


       SET finNumber=CONCAT(finNumber,'');
    END IF;


    SET inti=inti+1;
END WHILE;
RETURN CAST(finNumber AS SIGNED INTEGER) ;
ELSE
  RETURN 0;
END IF;


END$$



select uExtractNumberFromString('12;e1hhsak123s12');
12112312

MySQL - lower,upper char count

In one of my task I wanted to get the count of upper/lower char from a string but noticed that the string function provided in MySQL couldn't get me that.. and also at the moment it seems that the regex function in MySQL can match only and the matched stats cannot be captured nor returned.

I know 2 liner code in any scripting language can perform this tasks very well.


DELIMITER $$

DROP FUNCTION IF EXISTS `test`.`uGetLowerUpperCharCount`$$

CREATE FUNCTION `uGetLowerUpperCharCount`(prm_string varchar(250)) RETURNS varchar(250) CHARSET latin1
BEGIN
DECLARE strPos INT default 1;
DECLARE strUpperLen INT default 0;
DECLARE strLowerLen INT default 0;
DECLARE strNonAlphaLen INT default 0;
WHILE strPos <= LENGTH(prm_string) DO IF ASCII(SUBSTRING(prm_string,strPos ,1)) >= 65 AND ASCII(SUBSTRING(prm_string,strPos ,1)) <=90 THEN SET strUpperLen = strUpperLen+1; ELSEIF ASCII(SUBSTRING(prm_string,strPos ,1)) >= 97 AND ASCII(SUBSTRING(prm_string,strPos ,1)) <=122 THEN SET strLowerLen = strLowerLen+1; ELSE SET strNonAlphaLen = strNonAlphaLen+1; END IF; SET strPos = strPos+1; END WHILE; RETURN CONCAT('String ',prm_string,' has ', IF(strUpperLen>0,CONCAT(strUpperLen, ' - Upper Chars '),''),IF(strLowerLen>0,CONCAT(',',strLowerLen,' - Lower Chars '),''),IF(strNonAlphaLen>0,CONCAT(' and ',strNonAlphaLen,' - Non-Alpha '),''));
END$$

DELIMITER ;

Usage:

select uGetLowerUpperCharCount('Umesh Kumar Shastry');

String Umesh Kumar Shastry has 3 - Upper Chars ,14 - Lower Chars and 2 - Non-Alpha

MySQL - initcap function

Sometime back I was looking for a built-in initcap/ucfirst function in MySQL but unfortunately couldn't find such string functions so decided to write my own.. thanks to the MySQL community member who corrected the bug in my function & posted it back.


DELIMITER $$

DROP FUNCTION IF EXISTS `test`.`initcap`$$

CREATE FUNCTION `initcap`(x char(30)) RETURNS char(30) CHARSET utf8
BEGIN
SET @str='';
SET @l_str='';
WHILE x REGEXP ' ' DO
SELECT SUBSTRING_INDEX(x, ' ', 1) INTO @l_str;
SELECT SUBSTRING(x, LOCATE(' ', x)+1) INTO x;
SELECT CONCAT(@str, ' ', CONCAT(UPPER(SUBSTRING(@l_str,1,1)),LOWER(SUBSTRING(@l_str,2)))) INTO @str;
END WHILE;
RETURN LTRIM(CONCAT(@str, ' ', CONCAT(UPPER(SUBSTRING(x,1,1)),LOWER(SUBSTRING(x,2)))));
END$$

DELIMITER ;


Usage:

select initcap('umesh kumar shastry');

Umesh Kumar Shastry

select initcap('ashutosh s');

Ashutosh S

select initcap('rahul giri');

Rahul Giri

select initcap('alam seraj');

Alam Seraj

select initcap('atul kaushik');

Atul Kaushik

MySQL - Get bussiness/Off days

MySQL Version >5.0

Ever wondered how would you get a list of business dates/Off dates from a specific date range in MySQL? here is a simple way to do so...

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`uGetBussinessDays`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `uGetBussinessDays`(in_sDate DATE, in_eDate DATE)
READS SQL DATA
BEGIN
DECLARE l_sDate DATE;
SET l_sDate = in_sDate;
DROP TABLE IF EXISTS _tblBussinessDays;
DROP TABLE IF EXISTS _tblHolidaysDays;
CREATE TEMPORARY TABLE _tblBussinessDays(BussinessDays date);
CREATE TEMPORARY TABLE _tblHolidaysDays(Offdays date);
IF ( in_sDate > in_eDate ) THEN
SELECT "Invalid dates supplied";
END IF;
WHILE l_sDate <= in_eDate DO IF (DAYNAME(l_sDate) = 'Sunday' ) THEN INSERT INTO _tblHolidaysDays VALUES(l_sDate); ELSEIF ( DAYNAME(l_sDate) = 'Saturday' ) THEN INSERT INTO _tblHolidaysDays VALUES(l_sDate); ELSE INSERT INTO _tblBussinessDays VALUES(l_sDate); END IF; SET l_sDate = DATE_ADD(l_sDate,INTERVAL 1 DAY); END WHILE; SELECT BussinessDays FROM _tblBussinessDays; SELECT Offdays FROM _tblHolidaysDays; END$$ DELIMITER ;


## Now call above procedure to get a list of Bussiness days/Off days

call uGetBussinessDays('2009-06-01','2009-06-11');

BussinessDays
==========
2009-06-01
2009-06-02
2009-06-03
2009-06-04
2009-06-05
2009-06-08
2009-06-09
2009-06-10
2009-06-11

Offdays
======
2009-06-06
2009-06-07