| check for numbers only in varchar2 field [message #371735] |
Thu, 30 November 2000 09:22  |
Jerry Yost
Messages: 1
Registered: November 2000
|
Junior Member |
|
|
I need to select rows that only have numbers in a varchar2 field from that row. example below:
Field 1 Field 2
144450 MP RAD4.9 (079582) QFP
144475 MP RAMC 3-21 079-633 QFP
A4895134 RB-REP-VA SM PANEL
144H94 MP SNAMC 1.10
144507 MP RA-RS232 MASTER 3.02 QFP
I would like to retrieve rows 1,2 and 5. How can this be done in a SQL statement?
Thanks, Jerry
|
|
|
|
| Re: check for numbers only in varchar2 field [message #371742 is a reply to message #371735] |
Fri, 01 December 2000 03:20  |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
You could try
Select {list of fields}
From {table}
Where rtrim(translate(field_name,'0123456789',' ')) is null.
The translate converts the numbers to spaces, the Rtrim converts a string of all spaces to null, so if the string is purely numeric the test will work.
An approach which might be quicker would be to write your own function to do a To_number on the string, handle the exceptions, and pass back a 'Y' or 'N', then call this function in the SQL
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
