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 <chriswhite@interfuel.com> 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/mysql?unsub=ereed@nearfield.com

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

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/...-matching.html
http://dev.mysql.com/doc/refman/4.1/...functions.html
http://dev.mysql.com/doc/refman/4.1/en/regexp.html

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/...-matching.html
> http://dev.mysql.com/doc/refman/4.1/...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

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 <chriswhite@interfuel.com> 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/...-matching.html
>
http://dev.mysql.com/doc/refman/4.1/...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/mysql?unsub=ereed@nearfield.com