Home » SQL & PL/SQL » SQL & PL/SQL » Function (Oracle 10g)
Function [message #409570] Tue, 23 June 2009 02:38 Go to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Dear All,

I have a small clarification in order to create a function with the example shown below. I wonder whether it is possible.
Need to create a function with a single input variable which should be a character. Output should be the count of the characters.

for ex:

If i pass a character as abcd

The output should be the total of abcd (i.e) 10

The value should be assigned in such a way that
A holds the value 1
B holds the value 2
C holds the value 3
D holds the value 4
E holds the value 5
F holds the value 6
...
z holds the value 26


Thanks and Regards,
Hammer
Re: Function [message #409571 is a reply to message #409570] Tue, 23 June 2009 02:49 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
You will need to loop through the string for as many characters as you have to identifiy each character, use the substr function and use a case statement to assign each value required.
However, this could be done in a single SQL statement:
SELECT ASCII(SUBSTR('abcd',LEVEL,1))-96
FROM DUAL
CONNECT BY LEVEL <= LENGTH('abcd')
Re: Function [message #409572 is a reply to message #409570] Tue, 23 June 2009 02:51 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use a loop for 1 to LENGTH on the string, INSTR and SUBSTR to get the current character and ASCII function to get the value.

SQL Reference for all functions.

Regards
Michel
Previous Topic: Database Link: ORA-00604, ORA-20099, ORA-06512, ORA-02063
Next Topic: Order by for a specific category
Goto Forum:
  


Current Time: Tue Dec 06 12:26:24 CST 2016

Total time taken to generate the page: 0.17067 seconds