Home » SQL & PL/SQL » SQL & PL/SQL » Numbers to Alphabets (Oracle 10.2.0.4.0)
Numbers to Alphabets [message #589468] Sat, 06 July 2013 23:59 Go to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

I have a requirement to display numbers as equivalent alphabets , like if the stored value is 1 then it should display as 'A' ,2 means 'B' ,is there a way to find out.


CREATE TABLE APS ( ITEM_NO NUMBER, ITEM_NAME VARCHAR2(12))


INSERT INTO APS (ITEM_NO,ITEM_NAME) VALUES (1,'TEST1');

INSERT INTO APS (ITEM_NO,ITEM_NAME) VALUES (2,'TEST2');

INSERT INTO APS (ITEM_NO,ITEM_NAME) VALUES (3,'TEST3');

INSERT INTO APS (ITEM_NO,ITEM_NAME) VALUES (4,'TEST4');

-- The output to be is.

item_no , item_name
A          TEST1
B          TEST2
C          TEST3
D          TEST4


Re: Numbers to Alphabets [message #589469 is a reply to message #589468] Sun, 07 July 2013 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 58599
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You need to use ASCII and CHR functions.

Regards
Michel
Re: Numbers to Alphabets [message #589470 is a reply to message #589469] Sun, 07 July 2013 00:59 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

thanks michel , but is there any reference example.
Re: Numbers to Alphabets [message #589473 is a reply to message #589470] Sun, 07 July 2013 04:09 Go to previous messageGo to next message
dariyoosh
Messages: 531
Registered: March 2009
Location: Iran / France
Senior Member
What do you mean by Reference example exactly? In the documentation link provided you have both description and examples.

Regards,
Dariyoosh
Re: Numbers to Alphabets [message #589474 is a reply to message #589473] Sun, 07 July 2013 04:32 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

What i meant was any example .as i see chr(65)=A ,CHR(66)=B, CHR(67)=C and so on, but how i can use this logic to return the rows, by using decode or case statement whenever there is 1 Substitute it with CHR(65), I am just asking and i dont think there is harm in asking .

[Updated on: Sun, 07 July 2013 04:39]

Report message to a moderator

Re: Numbers to Alphabets [message #589475 is a reply to message #589474] Sun, 07 July 2013 04:38 Go to previous messageGo to next message
Michel Cadot
Messages: 58599
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We saw all that you have to see now just activate what's between your ears to write the query
We can't help you further, the next step is spoon.feed the query

Regards
Michel

[Updated on: Sun, 07 July 2013 04:39]

Report message to a moderator

Re: Numbers to Alphabets [message #589476 is a reply to message #589475] Sun, 07 July 2013 04:40 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

oh thanks michel , you mean to say dont ask anything from now on.
Re: Numbers to Alphabets [message #589477 is a reply to message #589474] Sun, 07 July 2013 04:42 Go to previous messageGo to next message
dariyoosh
Messages: 531
Registered: March 2009
Location: Iran / France
Senior Member
That being said, the mapping you described 1->A, 2->B, 3->C,..., 26->Z, gives only 26 possibilities. What if a number (item_no in the case of your example) is bigger than 26? You map it to what letter? Given that all letters have already their own associated key values (and assuming that item_no is the primary key).

Regards,
Dariyoosh
Re: Numbers to Alphabets [message #589478 is a reply to message #589477] Sun, 07 July 2013 04:45 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

thanks dariyoosh , the numbers will only upto the last alphabet z , i mean only 26 numbers will be used and its not a primary key , its just a field which i want to display.

[Updated on: Sun, 07 July 2013 04:46]

Report message to a moderator

Re: Numbers to Alphabets [message #589479 is a reply to message #589478] Sun, 07 July 2013 06:11 Go to previous messageGo to next message
John Watson
Messages: 4390
Registered: January 2010
Location: Global Village
Senior Member
You need to think, man! For example:

1. You could write a function that will except a number and return that number plus 65.
2. You could create a table with 26 rows, one column for the number and a second column with a letter, and perform a lookup.
3. You could write a searched case statement, with 26 possibilities.

That is three solutions. I'm sure there are more.

Re: Numbers to Alphabets [message #589480 is a reply to message #589479] Sun, 07 July 2013 06:13 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks john i went with the first option of writing a function that will add the number to CHR(65).
Re: Numbers to Alphabets [message #589481 is a reply to message #589480] Sun, 07 July 2013 06:15 Go to previous messageGo to next message
John Watson
Messages: 4390
Registered: January 2010
Location: Global Village
Senior Member
You should post your solution: that would help dispel the suspicion that you never write your own code.
Re: Numbers to Alphabets [message #589482 is a reply to message #589481] Sun, 07 July 2013 06:35 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

I hope i have dispelled the suspicion experts, i dont know why because of this strange suspicion my old gurus have stopped me from helping.


CREATE OR REPLACE FUNCTION get_rev_no (p_no IN NUMBER)
   RETURN CHAR
IS
BEGIN
   IF p_no = 1
   THEN
      RETURN (CHR (65));
   ELSE
      RETURN (CHR (65 + p_no-1));
   END IF;
-- End of Addition
END;
/


CREATE TABLE APS ( ITEM_NO NUMBER, ITEM_NAME VARCHAR2(12))


INSERT INTO APS (ITEM_NO,ITEM_NAME) VALUES (1,'TEST1');

INSERT INTO APS (ITEM_NO,ITEM_NAME) VALUES (2,'TEST2');

INSERT INTO APS (ITEM_NO,ITEM_NAME) VALUES (3,'TEST3');

INSERT INTO APS (ITEM_NO,ITEM_NAME) VALUES (4,'TEST4');


COMMIT;

SELECT GET_REV_NO(ITEM_NO),ITEM_NAME FROM APS;

GET_REV_NO(ITEM_NO)	ITEM_NAME
A	                 TEST1
B	                 TEST2
C	                 TEST3
D	                 TEST4


Re: Numbers to Alphabets [message #589483 is a reply to message #589482] Sun, 07 July 2013 07:33 Go to previous messageGo to next message
dariyoosh
Messages: 531
Registered: March 2009
Location: Iran / France
Senior Member
There is no need to implement a PL/SQL solution for this problem as it is simple SQL query. All you need to do is to read ASCII and CHR Their combinations (as it was said earlier) does the job.


Regards,
Dariyoosh
Re: Numbers to Alphabets [message #589486 is a reply to message #589483] Sun, 07 July 2013 09:27 Go to previous messageGo to next message
BlackSwan
Messages: 22512
Registered: January 2009
Senior Member
if you insist on PL/SQL solution below works as desired
CREATE OR replace FUNCTION Get_rev_no (p_no IN NUMBER) 
RETURN CHAR 
IS 
BEGIN 
    RETURN ( Chr (65 + p_no - 1) ); 
END; 

/ 


what about lowercase letters?
Re: Numbers to Alphabets [message #589487 is a reply to message #589486] Sun, 07 July 2013 10:48 Go to previous messageGo to next message
dariyoosh
Messages: 531
Registered: March 2009
Location: Iran / France
Senior Member
> what about lowercase letters?

There cannot be any, because he said only 26 letters are used in mapping. As a result no room is left for lowercase letters (or at least this is what I understood from his previous comment Smile )


Regards,
Dariyoosh

[Updated on: Sun, 07 July 2013 12:35]

Report message to a moderator

Re: Numbers to Alphabets [message #589488 is a reply to message #589487] Sun, 07 July 2013 13:07 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

thanks blackswan, dariyoosh,john and michel for the help , interested to know the sql solution.Well there is no lowercase.
Re: Numbers to Alphabets [message #589489 is a reply to message #589488] Sun, 07 July 2013 13:18 Go to previous messageGo to next message
dariyoosh
Messages: 531
Registered: March 2009
Location: Iran / France
Senior Member
arif_md2009 wrote on Sun, 07 July 2013 20:07
interested to know the sql solution.Well there is no lowercase.


Did you take a look at the examples given in the links I provided? and if yes, what did you make of them?


Regards,
Dariyoosh
Re: Numbers to Alphabets [message #589493 is a reply to message #589489] Sun, 07 July 2013 22:53 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

okay dariyoosh what do you mean , both of them does things differently , CHR returns number whereas ASCII Returns Character.
Re: Numbers to Alphabets [message #589494 is a reply to message #589493] Sun, 07 July 2013 23:58 Go to previous messageGo to next message
BlackSwan
Messages: 22512
Registered: January 2009
Senior Member
> CHR returns number whereas ASCII Returns Character.

After CHR() you can added a number to it; then use ASCII to convert the total back to a CHARACTER.
Re: Numbers to Alphabets [message #589511 is a reply to message #589494] Mon, 08 July 2013 02:46 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

thanks blackswan , for the prompt response.Do appreciate you effort and guidance.
Re: Numbers to Alphabets [message #589516 is a reply to message #589494] Mon, 08 July 2013 03:08 Go to previous messageGo to next message
dariyoosh
Messages: 531
Registered: March 2009
Location: Iran / France
Senior Member
BlackSwan wrote on Mon, 08 July 2013 06:58
After CHR() you can added a number to it; then use ASCII to convert the total back to a CHARACTER.


Maybe I didn't understand your comment correctly, but for me it's just the inverse. First you need to apply ASCII to get the ASCII code of the character, then you can add the proper number to the code and finally use CHR to convert the result to character.

Regards,
Dariyoosh
Re: Numbers to Alphabets [message #589520 is a reply to message #589516] Mon, 08 July 2013 04:04 Go to previous messageGo to next message
Michel Cadot
Messages: 58599
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This topic is now a total mess, I provide the "solution" to end it:
SQL> with data as (select level data from dual connect by level <= 10)
  2  select data, chr(ascii('A')+data-1) "CHAR" from data
  3  /
      DATA C
---------- -
         1 A
         2 B
         3 C
         4 D
         5 E
         6 F
         7 G
         8 H
         9 I
        10 J

Regards
Michel
icon14.gif  Re: Numbers to Alphabets [message #589527 is a reply to message #589520] Mon, 08 July 2013 04:38 Go to previous message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks Very Much All of you who supported this post.
Previous Topic: owa_text package
Next Topic: coding inside the trigger
Goto Forum:
  


Current Time: Mon Jul 28 04:51:23 CDT 2014

Total time taken to generate the page: 0.17537 seconds