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]