|
Home > Archive > MySQL ODBC Connector > April 2005 > extract numeric value from a string.
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
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@dixienet.it>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Eamon Daly 2005-04-23, 3:23 am |
| 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
----- Original Message -----
From: "dixie" <dixie@dixienet.it>
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 <dixie@dixienet.it>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql? unsub...
mediagroup.com
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| gerald_clark 2005-04-26, 9:23 am |
| 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@dixienet.it>
> To: "MySQL" <mysql@lists.mysql.com>
> Sent: Friday, April 22, 2005 6:18 PM
> Subject: extract numeric value from a string.
>
>
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Eamon Daly 2005-04-26, 11:24 am |
| 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@supplie
rsystems.com>
To: "Eamon Daly" <edaly@nextwavemedia.com>
Cc: "dixie" <dixie@dixienet.it>; "MySQL" <mysql@lists.mysql.com>
Sent: Tuesday, April 26, 2005 8:10 AM
Subject: Re: extract numeric value from a string.
> Eamon Daly wrote:
>
>
> Unless the string starts wit a '0'.
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
|
|
| mfatene@free.fr 2005-04-26, 1:23 pm |
| I just did the oposite :o)
set @a='0123FOO';
set @b=substring(0+conca
t('9',@a),- length(0+concat('9',
@a))+1);
select @b,replace(@a,@b,'')
;
have to concat a positive number !!!
Selon mfatene@free.fr:
> 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+conca
t('0',@a),- length(0+concat('0',
@a))+1);
> select @b,replace(@a,@b,'')
;
>
>
> Mathias
>
>
>
>
> Selon gerald_clark < gerald_clark@supplie
rsystems.com>:
>
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
|
|
|
|
|