Where like or in

[coping]

Oracle 10g

I have a table with a location field such as this:
MEX HQ/DF/MXMEX-HQ
MEM/TN/38194-7533
MIA LAC/FL/33126-0010

I want to select all the locations that contains a number. I've tried

INSERT INTO MYTABLE
SELECT location, r_num, r_dt
FROM TABLE1
WHERE location IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9');

WHERE location LIKE '%[0-9]%';

WHERE SUBSTR(location,20) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9');

I also want parse the field at the first number.

Any ideas?

[NogDog]

If using MySQL, you probably want to use the REGEXP function (http://dev.mysql.com/doc/refman/5.0/en/regexp.html).

[chazzy]

since we know you're using oracle, try looking at

http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/rischert_regexp_pt1.html

[NogDog]

since we know you're using oracle, try looking at

http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/rischert_regexp_pt1.html

Heh...I actually took a second look at the original post to see if it mentioned which DBMS, and for some reason my eyes must've ignored the first line. :rolleyes: Oh well, I blame it on old eyes.