Home » SQL & PL/SQL » SQL & PL/SQL » querying based on the string data in the column
querying based on the string data in the column [message #211782] Tue, 02 January 2007 04:37 Go to next message
sreehari
Messages: 101
Registered: May 2006
Senior Member
Hi all

In my table,i have colum with the name PARAMS of datatype varchar2(200)

it has the data as follows

PARAMS
------
12345
JJJJJ
66666
PPPPP
:
:
:

Now i need to fetch only those records which are having alphabets in PARAMS column.

the records i am expecting are

JJJJJ
PPPPP

How can i get the above result

Thanks
Re: querying based on the string data in the column [message #211785 is a reply to message #211782] Tue, 02 January 2007 04:45 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Use translate function to change numbers to space and keep a filter on not equal to space.
select translate('45asd6123456','123456',' ') from dual;
select * from table where translater(params,'0123456789',' ') <> ' ';
By
Vamsi
Re: querying based on the string data in the column [message #211786 is a reply to message #211782] Tue, 02 January 2007 04:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you want to get the rows where the column is all a-z and A-Z, then I'd do something like this:
  1  with mytable as (select 'AAAAAA' col_1 from dual union all
  2                   select 'AAA123' col_1 from dual union all
  3                   select 'BBBBBB' col_1 from dual union all
  4                   select '123456' col_1 from dual)
  5  select col_1
  6  from   mytable
  7* where  col_1 = translate(col_1,'1234567890','..........')
SQL> /

COL_1
------
AAAAAA
BBBBBB
This just replaces the numeric chrs in the string with '.' and checks to see if the result is different from the original.
Re: querying based on the string data in the column [message #211789 is a reply to message #211782] Tue, 02 January 2007 05:23 Go to previous messageGo to next message
bsureysh
Messages: 17
Registered: January 2007
Location: india
Junior Member
hi

SELECT * FROM table WHERE UPPER(PARAMS) > 'A' ;

try this..!!

~B.Suresh
Re: querying based on the string data in the column [message #211817 is a reply to message #211789] Tue, 02 January 2007 08:02 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
bsureysh wrote on Tue, 02 January 2007 06:23
hi

SELECT * FROM table WHERE UPPER(PARAMS) > 'A' ;



This is an incorrect solution.
foobar SCOTT> select foo1 from foo where upper(foo1) > 'A'
  2  /

FOO1
---------
A5
XYZ120

foobar SCOTT>
Re: querying based on the string data in the column [message #211824 is a reply to message #211817] Tue, 02 January 2007 08:23 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Problem is ambiguous.
Quote:
Now i need to fetch only those records which are having alphabets in PARAMS column.

Some of the solutions are giving the output by considering the problem to give not 'Number only' values.
The rest is giving the output by considering the problem to give 'Character only' values.

OP should select whichever he wants, as the test data is having the values either 'Number only' or 'Character only'.

By
Vamsi

PS: I might misread it.
Re: querying based on the string data in the column [message #211897 is a reply to message #211782] Wed, 03 January 2007 00:28 Go to previous message
bsureysh
Messages: 17
Registered: January 2007
Location: india
Junior Member
SELECT params FROM tablename WHERE params IS NOT NULL AND INSTR(TRANSLATE(params,'1234567890','$'),'$') = 0

check this..!!
Previous Topic: How to get Local System date ?
Next Topic: Logon Trigger Issue
Goto Forum:
  


Current Time: Sun Dec 04 10:46:57 CST 2016

Total time taken to generate the page: 0.11576 seconds