Good point. I assumed that number meant a real number. This should work for leading zeroes:
SELECT tag, @num := CONVERT(tag, SIGNED) AS cast_num, SUBSTRING(tag, 1, LOCATE(@num, tag) + LENGTH(@num) - 1) AS num_part, SUBSTRING(tag, LOCATE(@num, tag) + LENGTH(@num)) AS txt_part FROM tags;
+---------+----------+----------+----------+ | tag | cast_num | num_part | txt_part | +---------+----------+----------+----------+ | 1foo | 1 | 1 | foo | | 23bar | 23 | 23 | bar | | 234baz | 234 | 234 | baz | | 001quux | 1 | 001 | quux | +---------+----------+----------+----------+
Same concept, but the LOCATE finds the first occurrence of the casted number. Then add the length of the casted number et voila.
____________________________________________________________ Eamon Daly
----- Original Message ----- From: "gerald_clark" <[EMAIL PROTECTED]>
To: "Eamon Daly" <[EMAIL PROTECTED]>
Cc: "dixie" <[EMAIL PROTECTED]>; "MySQL" <mysql@lists.mysql.com>
Sent: Tuesday, April 26, 2005 8:10 AM
Subject: Re: extract numeric value from a string.
Eamon Daly wrote:
Easy enough. Get the numeric part via CONVERT, then get the rest of the string from the length of the numeric part, plus one:
SELECT tag, @num := CONVERT(tag, SIGNED) AS num_part, SUBSTRING(tag, LENGTH(@num) + 1) AS rest_of_string from tags; +--------+----------+----------------+ | tag | num_part | rest_of_string | +--------+----------+----------------+ | 1foo | 1 | foo | | 23bar | 23 | bar | | 234baz | 234 | baz | +--------+----------+----------------+ 3 rows in set (0.00 sec)
____________________________________________________________ Eamon Daly
Unless the string starts wit a '0'.
----- Original Message ----- From: "dixie" <[EMAIL PROTECTED]> To: "MySQL" <mysql@lists.mysql.com> Sent: Friday, April 22, 2005 6:18 PM Subject: extract numeric value from a string.
Hi at all, I've this necessity. In a table I've a field popolated by a string where the first (not costant lenght) part are number and the second part caracter. I want extract, in other field, the first part and the second in another field. There is a function to obtained it?
Tks in advance
Paolo -- dixie <[EMAIL PROTECTED]>
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
