Home » SQL & PL/SQL » SQL & PL/SQL » Where Like or In (Oracle 10g)  () 1 Vote
Where Like or In [message #352635] Wed, 08 October 2008 12:53 Go to next message
theladyd
Messages: 13
Registered: September 2008
Junior Member
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?
Re: Where Like or In [message #352636 is a reply to message #352635] Wed, 08 October 2008 13:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
REGEXP_LIKE

please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel

[Updated on: Wed, 08 October 2008 13:01]

Report message to a moderator

Re: Where Like or In [message #352637 is a reply to message #352635] Wed, 08 October 2008 13:02 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
You'll need the TRANSLATE function. Search on this site.
Re: Where Like or In [message #352647 is a reply to message #352636] Wed, 08 October 2008 15:07 Go to previous message
theladyd
Messages: 13
Registered: September 2008
Junior Member
Thanks Michel! It worked perfectly.
Previous Topic: Adding Column Values
Next Topic: Updating a varchar2 field
Goto Forum:
  


Current Time: Tue Dec 06 08:15:39 CST 2016

Total time taken to generate the page: 0.10653 seconds