How to correctly use SUBSTR, INSTR and or REPLACE in Oracle 11G

Question

What I would like to do is getting the third string. in this example:

NL 123456789 TUE

that is changed in this example to a number:

2927 123456789 353620

to be (re)placed in the back. So behind the '2927'. I am wondering how to do this? The code that I have at the moment for getting this string is:

CREATE OR REPLACE FUNCTION generateISIN(
  countryCode Country.Code%TYPE,
  universityCode University.Code%TYPE,
  studentNumber VARCHAR2)
RETURN VARCHAR2
AS
  newStudentNumber VARCHAR2(50) := '';
BEGIN
 select country.code || ' ' || studentNumber || ' ' || university.code
 into newStudentNumber
 from country, university
 where country.code = countryCode
 and university.code = universityCode;
  dbms_output.put_line(newStudentNumber);

  RETURN newStudentNumber;
END generateISIN;
/

this is my select statement for getting the first example

select generateISIN('NL','TUE', '123456789') from dual;

and this is my second select statement that turns the string into numbers (did not put the code loop in here)

select CONVERT_STR_TO_NUMERIC(generateISIN('NL','TUE', '123456789')) from dual;

If it is not too much to ask. Please provide me an example with my given problem

I hope my question is not too vague

Thanks in advance

Answer 1

If you want to use INSTR and SUBSTR the following should do it:

SELECT SUBSTR (x, INSTR(x, ' ', 1, 2) +1) || ' ' ||
       SUBSTR (x, 1, INSTR(x, ' ') -1)    || ' ' ||
       SUBSTR (x, INSTR(x, ' ') +1, INSTR(x, ' ', 1, 2) -INSTR(x, ' '))
 FROM (SELECT 'NL 123456789 TUE' x 
         FROM dual);

Though I would rather use oracle regexp_replace:

SELECT regexp_replace('NL 123456789 TUE','(.+) (.+) (.+)','\3 \1 \2')
  FROM dual;

Check SQL Fiddle here.