Re: Number extraction from a string
Chris, On Thursday 07 December 2006 16:34, Ed Reed wrote: Thanks for the quick reply Chris. It's close but it's a little off. Your example also returns all instances that where the letter N exists in another words as well SELECT SUBSTRING(value,2) as value_num, value FROM num_test WHERE value like 'N%'; Though this wouldn't work if you had like Night or NIGHT. Is that going to be a choice? You'll need a regex, see these... http://dev.mysql.com/doc/refman/4.1/en/pattern-matching.html http://dev.mysql.com/doc/refman/4.1/en/string-comparison-functions.html http://dev.mysql.com/doc/refman/4.1/en/regexp.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number extraction from a string
On Friday 08 December 2006 01:57, Philip Mather wrote:
You'll need a regex, see these...
http://dev.mysql.com/doc/refman/4.1/en/pattern-matching.html
http://dev.mysql.com/doc/refman/4.1/en/string-comparison-functions.html
http://dev.mysql.com/doc/refman/4.1/en/regexp.html
Yes, you could use a regex and it would work, but if the format of N### is
persistant and there are no false positives than I'd rather use that instead
of regexes, which can an intensive operation. Also you might want to try:
SUBSTR(value,2) instead of LIKE 'N%' just to see how they compare. If a regex
is required, you could have something like:
SELECT SUBSTRING(value,2) as value_num, value FROM num_test WHERE value x;
where x is one of the following depending on the situation:
REGEX('N[0-9]+$')
REGEX('N[0-9]+')
REGEX('N[0-9]{3}$')
depends on how specific you want to get really.
--
Chris White
PHP Programmer
Interfuel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number extraction from a string
Is there anyway to use RegExp in a field parameter? What would be great
is if I could do this,
SELECT Trim(Both RegExp '[a-z]' From value) as value_num, value
FROM num_test
WHERE value REGEXP 'N[1-999]';
The biggest problem is trying to find the position of where the number
starts in the string so that the number by itself can be shown as
returned field.
Chris White [EMAIL PROTECTED] 12/8/06 8:08 AM
On Friday 08 December 2006 01:57, Philip Mather wrote:
You'll need a regex, see these...
http://dev.mysql.com/doc/refman/4.1/en/pattern-matching.html
http://dev.mysql.com/doc/refman/4.1/en/string-comparison-functions.html
http://dev.mysql.com/doc/refman/4.1/en/regexp.html
Yes, you could use a regex and it would work, but if the format of N###
is
persistant and there are no false positives than I'd rather use that
instead
of regexes, which can an intensive operation. Also you might want to
try:
SUBSTR(value,2) instead of LIKE 'N%' just to see how they compare. If
a regex
is required, you could have something like:
SELECT SUBSTRING(value,2) as value_num, value FROM num_test WHERE value
x;
where x is one of the following depending on the situation:
REGEX('N[0-9]+$')
REGEX('N[0-9]+')
REGEX('N[0-9]{3}$')
depends on how specific you want to get really.
--
Chris White
PHP Programmer
Interfuel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
Number extraction from a string
Can someone help me with this? I have a text field that sometimes contains a number preceded with the letter N. So it might look like this A test N60 or N45 someother text or This happened. N122, Then there was this. I need to come up with a Select statement that can show me all the instances where there is a N### value and return the number ### as a separate field. Anyone have a quick and easy solution? Thanks
Re: Number extraction from a string
On Thursday 07 December 2006 14:57, Ed Reed wrote:
Can someone help me with this?
quick hack, but it works from what you've stated:
mysql create table num_test (id SERIAL PRIMARY KEY, value VARCHAR(256));
Query OK, 0 rows affected (0.07 sec)
mysql insert into num_test (value) VALUES ('N400'),('400'),('300'),('N500');
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql SELECT REPLACE(value,'N','') as value_num, value FROM num_test WHERE
POSITION('N' IN value);
+---+---+
| value_num | value |
+---+---+
| 400 | N400 |
| 500 | N500 |
+---+---+
2 rows in set (0.00 sec)
--
Chris White
PHP Programmer
Interfuel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number extraction from a string
Thanks for the quick reply Chris.
It's close but it's a little off. Your example also returns all
instances that where the letter N exists in another words as well
Do you know of a way to prevent that?
Thanks again
Chris White [EMAIL PROTECTED] 12/7/06 3:11 PM
On Thursday 07 December 2006 14:57, Ed Reed wrote:
Can someone help me with this?
quick hack, but it works from what you've stated:
mysql create table num_test (id SERIAL PRIMARY KEY, value
VARCHAR(256));
Query OK, 0 rows affected (0.07 sec)
mysql insert into num_test (value) VALUES
('N400'),('400'),('300'),('N500');
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql SELECT REPLACE(value,'N','') as value_num, value FROM num_test
WHERE
POSITION('N' IN value);
+---+---+
| value_num | value |
+---+---+
| 400 | N400 |
| 500 | N500 |
+---+---+
2 rows in set (0.00 sec)
--
Chris White
PHP Programmer
Interfuel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number extraction from a string
On Thursday 07 December 2006 16:34, Ed Reed wrote: Thanks for the quick reply Chris. It's close but it's a little off. Your example also returns all instances that where the letter N exists in another words as well SELECT SUBSTRING(value,2) as value_num, value FROM num_test WHERE value like 'N%'; Though this wouldn't work if you had like Night or NIGHT. Is that going to be a choice? -- Chris White PHP Programmer Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
