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 …
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 …
This post is about a PL/SQL feature that doesn't get enough respect, "invoker's rights".First off, what's its real name? Depending on the source, you'll see the feature name spelled "invoker's rights", "invokers' rights", or "invoker rights". That makes a difference -- you'll get different results in Google depending on what combination of singular, plural, and possessive you use. And to be
Here's another ode to a small but fundamental aspect of Oracle, following the same theme as The Humble IF Statement. This time, let's look at the COUNT( ) function. I think when you look at it the right way, it opens up the whole story about database performance.What's the first thing you do when poking around an unfamiliar system? I'll bet it involves SELECT COUNT(*) queries in one way or
Constraints are simultaneously one of my most favorite and least favorite Oracle Database features. They're great for keeping bad data out of the database. They're a terrible imposition on object-oriented, agile, or <insert your favorite buzzword here> coding style. They save a ton of repetitive coding, writing the same logic in different languages. Hey, we already wrote all that redundant code
At OOW, I ran into Stephen Feuerstein after seeing him demonstrate Quest Software's "Quest Code Tester" product. Considering how I might use a product like that for testing web-based applications, I suggested a couple of enhancements.The biggest, most important procedures that I test in PL/SQL are those that generate entire web pages. For that kind of testing, you can't look at whether data has