SEO

#1 (**permalink**) 02-28-2008, 05:22 AM

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

#2 (**permalink**) 02-28-2008, 05:22 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Number extraction from a string