PL/SQL convert string to number

Question

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!

What result are you expecting when you convert this string to a number? Should the number be 123456789 or something else? — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Apr 29, 2015 at 15:37
@BobJarvis I thought that all chars would be converted to a number, but my assignment description says A =16, B=17, C=18, ... — Jules
– Jules, Commented Apr 29, 2015 at 15:39
You'll need to convert it character-by-character then, using whatever mapping you've been given; presumably with even numeric digits mapped somehow. Depending on the rules you might be able to use an offset from ASCII for some characters, but not all. And then presumably sum the numeric value for each character, if this is for a checksum? — Alex Poole
– Alex Poole, Commented Apr 29, 2015 at 15:58
@BobJarvis First I need to convert every character to a number, than I have to delete all blank spaces — Jules
– Jules, Commented Apr 29, 2015 at 16:15

2 revs, 2 users 97% · Accepted Answer · 2019-06-18 22:14:47Z

3

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'.

edited Jun 18, 2019 at 22:14

community wiki

2 revs, 2 users 97%
Bob Jarvis

Sign up to request clarification or add additional context in comments.

3 Comments

Jules Over a year ago

As I discovered today I could also use the ASCII method and do that numer -49

Maxime de Lange Over a year ago

Can I also reverse this function? That I generate letters from numbers (instead of numbers from letters)?

Bob Jarvis - Слава Україні Over a year ago

Perhaps. One problem to overcome would be knowing whether a string of numbers should be converted to ASCII or left alone. I'm not sure how you'd know that, but perhaps you can figure something out. Best of luck.

Sendi_t · Accepted Answer · 2015-04-29 18:13:41Z

2

can you not just use

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

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

answered Apr 29, 2015 at 18:13

Sendi_t

6476 silver badges14 bronze badges

1 Comment

Alex Poole Over a year ago

Not if the assignment specifies the algorithm to use, as the comments suggest.

Misha Brukman · Accepted Answer · 2015-07-11 15:54:50Z

2

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;

edited Jul 11, 2015 at 15:54

Misha Brukman

13.5k5 gold badges66 silver badges85 bronze badges

answered Jul 11, 2015 at 15:49

Ivan

211 bronze badge

Collectives™ on Stack Overflow

PL/SQL convert string to number

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related