Hi all,
The trick i can see if the string start with '0' is to make it starting with a
positive number.
FOr example if my string is '0123FOO' :
set @a='0123FOO';
set @b=substring(0+concat('0',@a),-length(0+concat('0',@a))+1);
select @b,replace(@a,@b,'');
Mathias
Selon gerald_clark <gerald_clark@stripped>:
> 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>
> >
> >
> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
>
