Home » RDBMS Server » Server Administration » check for numbers only in varchar2 field
check for numbers only in varchar2 field [message #371735] Thu, 30 November 2000 09:22 Go to next message
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

Report message to a moderator

Re: check for numbers only in varchar2 field [message #371742 is a reply to message #371735] Fri, 01 December 2000 03:20 Go to previous messageGo to next message
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

Report message to a moderator

Re: check for numbers only in varchar2 field [message #371743 is a reply to message #371735] Fri, 01 December 2000 03:21 Go to previous message
John R
Messages: 156
Registered: March 2000
Senior Member
One correction to the previous post.

Due to the editor for these posts being a bit in the pants side, the 3rd string in the TRANSLATE command has come out as '', whereas it should have been a string of 10 spaces.

Report message to a moderator

Previous Topic: Help..URGENT !!
Next Topic: Nesting Packages
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Jan 03 12:24:10 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.