Home » SQL & PL/SQL » SQL & PL/SQL » Alphanumeric (Oracle9i)
Alphanumeric [message #435107] Mon, 14 December 2009 06:07 Go to next message
prakashaa
Messages: 31
Registered: November 2009
Location: Bangalore
Member
Hi experts
How can we display alphanumeric data in a column in oracle 9i.

SNO SNAME
ABC1 Ram
1234 Sym
1122 Kiran
abc1 Raj
6542 Ravi
abc2 Suji
ABC3 Mari
abc4 Kumar

How can we display the SNO numbers that contain alphanumericdata
in lower case.

The output like this

abc1
abc2
abc4

Please help me thanka in advance.
Re: Alphanumeric [message #435111 is a reply to message #435107] Mon, 14 December 2009 06:32 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Use Lower function in your select statement.
Re: Alphanumeric [message #435179 is a reply to message #435111] Tue, 15 December 2009 00:33 Go to previous messageGo to next message
prakashaa
Messages: 31
Registered: November 2009
Location: Bangalore
Member
Hi experts
How can we display alphanumeric data in a column in oracle 9i.

SNO SNAME
ABC1 Ram
1234 Sym
1122 Kiran
abc1 Raj
6542 Ravi
abc2 Suji
ABC3 Mari
abc4 Kumar

How can we display all the SNO numbers that contain alphanumericdata


The output like this

ABC1
abc1
abc2
ABC3
abc4

Please help me.
Re: Alphanumeric [message #435183 is a reply to message #435179] Tue, 15 December 2009 00:45 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
I think there is no inbuilt function, you can use directly.

You have to develop your own logic for this using user defined function.

regards,
Delna
Re: Alphanumeric [message #435187 is a reply to message #435179] Tue, 15 December 2009 01:09 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Search For IS_NUMERIC function in this forum.

Sriram Smile
Re: Alphanumeric [message #435188 is a reply to message #435179] Tue, 15 December 2009 01:10 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What did you do or try since your last post?

Hint: Use TRANSLATE to remove figures, if the rest is not null then field contains alphabetic characters.

Regards
Michel

[Updated on: Tue, 15 December 2009 01:17]

Report message to a moderator

Re: Alphanumeric [message #435190 is a reply to message #435188] Tue, 15 December 2009 01:15 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Yes that is what i am going to post.But Unfortunate..

So in addition read this for the function..from 8i http://download.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/function.htm


sriram Smile

[Updated on: Tue, 15 December 2009 01:17] by Moderator

Report message to a moderator

Re: Alphanumeric [message #435289 is a reply to message #435187] Tue, 15 December 2009 07:41 Go to previous messageGo to next message
prakashaa
Messages: 31
Registered: November 2009
Location: Bangalore
Member

Hi experts
If the data contains like this in that case TRANSLATE won't work.

SNO SNAME
ABC1 Ram
1234 Sym
1122 Kiran
abc1 Raj
6542 Ravi
abc2 Suji
ABC3 Mari
abc4 Kumar
abc Naik
abcd kishor
09/OCT/08 Hrutik
08:50 Ulol
R#K-D Raman
05DEF Karun

How can we display all the SNO numbers that contain alphanumericdata,and the numaric only on the right hand side.


The output like this

ABC1
abc1
abc2
ABC3
abc4

Please help me.
Re: Alphanumeric [message #435290 is a reply to message #435289] Tue, 15 December 2009 07:46 Go to previous message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to CLEARLY specify what you have and what you want if you want an appropiate answer.
And you will have faster answer if you post a test case we can work with.

Regards
Michel
Previous Topic: query help
Next Topic: swap
Goto Forum:
  


Current Time: Tue Sep 27 17:55:34 CDT 2016

Total time taken to generate the page: 0.10584 seconds