Carsten Pedersen (Certification Manager of at MySQL AB) read my entry about stripping digits, and has done a good write up here on how this function could be improved, a great deal.

Have a read of Carstens link, and my previous entry if you didn’t catch it, before we progress.

As you see, I mentioned that “it performed fairly well”, but I admit given the nature of the request (a quick IRC question) I didn’t run a benchmark on it compared to something like Carsten’s REPLACE solution. I actually went with the REGEXP as the original question was actually something along the lines of “If I have a string such as “joe123″ how I can I strip the digits from the end to return just “joe”. I wrote the quick function as an example of what you can do in 5.0, as the user was still 4.x anyway, really the answer would have been a little more …

Carsten Pedersen (Certification Manager of at MySQL AB) read my entry about stripping digits, and has done a good write up here on how this function could be improved, a great deal.

Have a read of Carstens link, and my previous entry if you didn’t catch it, before we progress.

As you see, I mentioned that “it performed fairly well”, but I admit given the nature of the request (a quick IRC question) I didn’t run a benchmark on it compared to something like Carsten’s REPLACE solution. I actually went with the REGEXP as the original question was actually something along the lines of “If I have a string such as “joe123″ how I can I strip the digits from the end to return just “joe”. I wrote the quick function as an example of what you can do in 5.0, as the user was still 4.x anyway, really the answer would have been a little more …

We had a question in #mysql on freenode yesterday, asking if there was a function to strip digits from a string. The answer is of course - not natively.

I’ve been playing around with Functions and Stored Procedures a bit lately though, trying to familiarise myself with the MySQL implementation fully, and wrote this quick function which does the job, although only in 5.0:

DELIMITER //
 
DROP FUNCTION strip_digits //
 
CREATE FUNCTION strip_digits (str VARCHAR(50))
  RETURNS VARCHAR(50)
  BEGIN
    DECLARE sub_start INT DEFAULT 0;
    DECLARE res VARCHAR(50) DEFAULT '';
    
    WHILE sub_start < LENGTH(str)+1 DO
    
     IF SUBSTRING(str,sub_start,1) REGEXP '[[:alpha:]]' THEN
…

We had a question in #mysql on freenode yesterday, asking if there was a function to strip digits from a string. The answer is of course - not natively.

I’ve been playing around with Functions and Stored Procedures a bit lately though, trying to familiarise myself with the MySQL implementation fully, and wrote this quick function which does the job, although only in 5.0:

DELIMITER //

DROP FUNCTION strip_digits //

CREATE FUNCTION strip_digits (str VARCHAR(50))
RETURNS VARCHAR(50)
BEGIN
DECLARE sub_start INT DEFAULT 0;
DECLARE res VARCHAR(50) DEFAULT '';

WHILE sub_start < LENGTH(str)+1 DO

IF SUBSTRING(str,sub_start,1) REGEXP '[[:alpha:]]' THEN
SET res = CONCAT(res,SUBSTRING(str,sub_start,1));
END IF;

SET sub_start = sub_start + 1;

END WHILE;
RETURN res;
END;
//

Here’s a couple …

I mentioned “fuzzy foreign key” matches in my post the other day, and thought that I’d give it a crack tonight. The aim of the fuzzy match is to try and find relationships when a) They are InnoDB and haven’t been created or b) Foreign keys are not avilable to the storage engine that is being used.

Most DBA’s will know their schemas pretty well, this however should be pretty handy for consultants or anyone starting fresh at a new job / on a new database etc.

It should be noted that this will only “fuzzy” match against a schema that follows strict naming conventions - Where the tablename and “id” is used for the primary key column, and the same name is used for the foreign key in the child table. For instance, given the following example tables:

mysql> desc company;
+————–+————–+——+—–+———+—————-+
| Field        | Type         | Null | Key | Default | Extra …

I mentioned “fuzzy foreign key” matches in my post the other day, and thought that I’d give it a crack tonight. The aim of the fuzzy match is to try and find relationships when a) They are InnoDB and haven’t been created or b) Foreign keys are not avilable to the storage engine that is being used.

Most DBA’s will know their schemas pretty well, this however should be pretty handy for consultants or anyone starting fresh at a new job / on a new database etc.

It should be noted that this will only “fuzzy” match against a schema that follows strict naming conventions - Where the tablename and “id” is used for the primary key column, and the same name is used for the foreign key in the child table. For instance, given the following example tables:

<code>&lt;blockquote&gt;<br />
mysql&gt; desc company;<br /> …

http://www.oracle.com/timesten/index.html

This seems like an interesting move from Oracle. Times Ten’s Cache is an in-memory cache type database that works very much like MySQL Cluster - with read/write functionality - along with the ability to “divert” to a backend (Oracle) database when the data needed is not memory resident.

It will be interesting over the next few months to see what Oracle does with this. On the surface it’s quite obvious that it’s an attempt to get their foot in the door with the likes of telcos and financial services companies, for their “frontline” systems, where they are rather lacking due to the cumbersome nature of the beast for “real time” data.

So you may have read this through the MySQL category on my blog, and wondered “How the hell does this relate to MySQL”? As any seasoned MySQL user would know, …