CODEWITHSUNDEEP
oracle-databaseplsqloracle11gconverters
Jules
Jules

Reputation: 544

PL/SQL convert string to number

I want to convert a string to numbers for a checksum. The string consists of universityCode || ' ' || countryCode || ' ' || studentNumber.

An example string could be:

TUE NL 123456789

I have to convert this sample string to numbers, and tried this with the TO_NUMBER function, but I keep getting an error.

This is my code to create (or replace) a function in PL/SQL:

-- Een functie om een International Student Identification Number (ISIN) te genereren
CREATE OR REPLACE FUNCTION generateISIN(
  countryCode Country.Code%TYPE,
  universityCode University.Code%TYPE,
  studentNumber VARCHAR2
)
RETURN VARCHAR2
AS
  newStudentNumber VARCHAR2(50) := '';
BEGIN
-- Zorgen voor de goede waarde voordat we de checksum beginnen
newStudentNumber := universityCode || ' ' || countryCode || ' ' || studentNumber;
-- newStudentNumber omzetten naar enkel getallen
newStudentNumber := TO_NUMBER(newStudentNumber);
-- Spaties weghalen in newStudentNumber
newStudentNumber := TRIM(' ' FROM newStudentNumber);
  RETURN newStudentNumber;
END;
/

Could someone help me with my problem? Thanks a lot in advance!

Upvotes: 1

Views: 7555

Answers (3)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

It looks like you're supposed to convert the initial string, which may/will contain alphabetic characters, to another string containing only numeric characters. Then you've got some other messing around to do, but the following may get you started:

FUNCTION CONVERT_STR_TO_NUMERIC(pin_Str IN VARCHAR2)
  RETURN VARCHAR2
IS
  strResult  VARCHAR2(32767);
  c          CHAR(1);
BEGIN
  FOR i IN 1..LENGTH(pin_Str) LOOP
    c := SUBSTR(pin_Str, i, 1);

    strResult := strResult || CASE c
                                WHEN 'A' THEN '16'
                                WHEN 'B' THEN '17'
                                WHEN 'C' THEN '18'
                                WHEN 'D' THEN '19'
                                WHEN 'E' THEN '20'
                                WHEN 'F' THEN '21'
                                WHEN 'G' THEN '22'
                                WHEN 'H' THEN '23'
                                WHEN 'I' THEN '24'
                                WHEN 'J' THEN '25'
                                WHEN 'K' THEN '26'
                                WHEN 'L' THEN '27'
                                WHEN 'M' THEN '28'
                                WHEN 'N' THEN '29'
                                WHEN 'O' THEN '30'
                                WHEN 'P' THEN '31'
                                WHEN 'Q' THEN '32'
                                WHEN 'R' THEN '33'
                                WHEN 'S' THEN '34'
                                WHEN 'T' THEN '35'
                                WHEN 'U' THEN '36'
                                WHEN 'V' THEN '37'
                                WHEN 'W' THEN '38'
                                WHEN 'X' THEN '39'
                                WHEN 'Y' THEN '40'
                                WHEN 'Z' THEN '41'
                                ELSE c
                              END;
  END LOOP;

  RETURN strResult;
END CONVERT_STR_TO_NUMERIC;

For example, if you call the above with your test string of 'TUE NL 123456789' it produces '353620 2927 123456789'.

Upvotes: 3

Ivan
Ivan

Reputation: 21

I found this and it works perfectly:

string VARCHAR2(50) := '';
temp VARCHAR2(50) := '';

for i in 1..length(string) loop
        temp := temp||(ascii(substr(string,i,1))-ascii('A')+16);  
     end loop;

Upvotes: 2

Sendi_t
Sendi_t

Reputation: 637

can you not just use

SQL> select ora_hash( "mycode" )  from dual;

ORA_HASH('mycode')
----------------------
            2519249214

Upvotes: 2

Related Questions