How can I reverse a string in PL/SQL?

[alrightydooda]

Hi,

Is there a PL/SQL equvalent to the strReverse function in VB? I'm tring to check a string for the existance of a substring and i need to check it forwards and in reverse order.

e.g:

Here is the code I have for checking if r_map.surface_code exists in psurfacecode, but what I am asking is how do I check if r_map.surface_code exists in the reverse of psurfacecode?

CURSOR c_map
IS
SELECT UNIQUE s.code, s.surface_code, s.disp_order
FROM dhs_tooth_surfaces s, dhs_tooth_surface_mapping m
WHERE s.code = m.surface_code_client
AND s.active = -1
AND m.tooth_no = pareacode
ORDER BY s.disp_order;

r_map c_map%ROWTYPE;
BEGIN
displaycode := '';

OPEN c_map;

FETCH c_map
INTO r_map;

WHILE c_map%FOUND
LOOP
----------------------------
--!!THIS IS THE LINE!!
IF INSTR (psurfacecode, r_map.surface_code) > 0
-----------------------------
THEN
displaycode := displaycode || r_map.code;
END IF;

FETCH c_map
INTO r_map;
END LOOP;

CLOSE c_map;

RETURN (displaycode);

Thanks in advance!

 

[alrightydooda]

Here is a clearer example of what I'm looking for:

SELECT INSTR('MRFL', 'RF') REV_SRCH
FROM dual;

This returns 2.

SELECT INSTR('LFRM', 'RF') REV_SRCH
FROM dual;

This returns 0. I'm looking for a function to reverse this variable (LFRM) so that it matches the first example.

 

[carp]

You will need to create your own function - something like

CREATE OR REPLACE FUNCTION reverse_string(p_string IN VARCHAR2) RETURN VARCHAR2 IS
   l_string VARCHAR2(32000) := NULL;
BEGIN
   FOR i IN REVERSE 1..LENGTH(p_string) LOOP
      l_string := l_string||substr(p_string,i,1);
   END LOOP;
   RETURN l_string;
END;
/

Then you can invoke the function in your call as:

08:29:17 SQL> select instr(reverse_string('LFRM'),'RF') from dual;

INSTR(REVERSE_STRING('LFRM'),'RF')
----------------------------------
                                 2

 

[alrightydooda]

Thanks carp that worked a treat!