Home » SQL & PL/SQL » SQL & PL/SQL » Filter the Character (Oracle 9i, Form and Report 6i)
Filter the Character [message #434954] Sat, 12 December 2009 01:18 Go to next message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member

Hi,

I required to filter the character Field Which have the Numbers also.

Please reply me.

Regards,

C V S
Re: Filter the Character [message #434959 is a reply to message #434954] Sat, 12 December 2009 01:59 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post a working Test case: create table and insert statements along with the result you want with these data.
Please read OraFAQ Forum Guide
Please search before posting, this has been answered many times.
Please always post your Oracle version with 4 decimals.

Regards
Michel
Re: Filter the Character [message #434962 is a reply to message #434954] Sat, 12 December 2009 02:40 Go to previous messageGo to next message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member

Hi,

I required to filter the character (Alphabetic) Field Which have the Numbers also.

Example
With Alphabetic Without Alphabetic
GRINNO CHALLANNO CHALLANNO
92200035 1a 1
92200056 5371a 5371
92200057 5371b 5371
92200058 5371c 5371
92200060 5372a 5372
92200069 003A 003
92200083 13a 13
92200090 039081a 039081
92200117 5169a 5169

Please reply me.

Regards,

C V S
Re: Filter the Character [message #434963 is a reply to message #434962] Sat, 12 December 2009 02:50 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Michel already suggested you in your previous topic.
Don`t you get anything from that?

Show us what you tried so far?

sriram.
Re: Filter the Character [message #434964 is a reply to message #434962] Sat, 12 December 2009 02:51 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post a test case and follow the guide, "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel

[Updated on: Sat, 12 December 2009 02:51]

Report message to a moderator

Re: Filter the Character [message #435052 is a reply to message #434962] Mon, 14 December 2009 00:47 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Also, specify clearly, what output do you want.

regards,
Delna
Re: Filter the Character [message #435079 is a reply to message #435052] Mon, 14 December 2009 04:01 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Hey Cvs ,

check following link http://forums.oracle.com/forums/thread.jspa?threadID=662335

create table rb_test (col1  VARCHAR2(100));

insert into rb_test (col1 ) values ( 'A1');
insert into rb_test (col1 ) values ( '1');
insert into rb_test (col1 ) values ( 'A2');
insert into rb_test (col1 ) values ( '3');

Commit;

select col1,DECODE( TRANSLATE(col1,'0123456789',' '), NULL, 'number','contains char') from  rb_test
/

COL1                                                                                                 DECODE(TRANSL
---------------------------------------------------------------------------------------------------- -------------
A1                                                                                                   contains char
2                                                                                                    number
A2                                                                                                   contains char
3                                                                                                    number




Regards,
Rahul
Re: Filter the Character [message #435086 is a reply to message #434954] Mon, 14 December 2009 04:14 Go to previous messageGo to next message
shettypravs
Messages: 9
Registered: August 2008
Junior Member
If you are working on Oracle 10g then

select * from table_name where regexp_like (<column_name>,':digit:')

will yield all rows having numeric data values in <column_name>

[Updated on: Mon, 14 December 2009 04:16]

Report message to a moderator

Re: Filter the Character [message #435091 is a reply to message #435086] Mon, 14 December 2009 04:22 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
there you go Cool

in continuation with last code....

select col1, case when regexp_like(col1, '^-?[[:digit:],.]*$') then 'Numeric' else 'Non-Numeric' end as type
from rb_test

COL1                                                                                                 TYPE
---------------------------------------------------------------------------------------------------- -----------
A1                                                                                                   Non-Numeric
1                                                                                                    Numeric
A2                                                                                                   Non-Numeric
3                                                                                                    Numeric




Thanks shettypravs , Just wanted to Add snippet.

Regards,
Rahul
Re: Filter the Character [message #435098 is a reply to message #435091] Mon, 14 December 2009 05:07 Go to previous message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you post a COL1 column with 100 char width when it only contains at most 2 characters?

Regards
Michel
Previous Topic: Writing File in PL/SQL (newline)
Next Topic: Running OS script file from PL/SQL through java class.
Goto Forum:
  


Current Time: Fri Sep 30 00:31:29 CDT 2016

Total time taken to generate the page: 0.14723 seconds