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" <gerald_clark@stripped>
To: "Eamon Daly" <edaly@stripped>
Cc: "dixie" <dixie@stripped>; "MySQL" <mysql@stripped>
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" <dixie@stripped>
>> To: "MySQL" <mysql@stripped>
>> 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 <dixie@stripped>
>>
>>
>>
>
