I just did the oposite :o)
set @a='0123FOO';
set @b=substring(0+concat('9',@a),-length(0+concat('9',@a))+1);
select @b,replace(@a,@b,'');
have to concat a positive number !!!
Selon [EMAIL PROTECTED]:
> 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 <[EMAIL PROTECTED]>:
>
> > 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]
> >
> >
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
