Re: Partial string comparison in DECODE ?

You could create a function that takes to string to decode as a parameter then returns the 'decoded' result. The function could then contain the same logic as you have described as it would be written in PL/SQL and not limited to what SQL can do.

CREATE OR REPLACE PACKAGE BODY my_package AS FUNCTION my_decode(p1 VARCHAR2);
pragma restrict_references(my_decode, WNPS, WNDS); END; CREATE OR REPLACE PACKAGE BODY my_package AS FUNCTION my_decode(p1 VARCHAR2) AS
BEGIN
   IF SUBSTR(p1, 1, 6) = 'T123456' THEN RETURN 'answer 1'; END IF;    IF (SUBSTR ... etc
   RETURN NULL;
END;
END; Then:

SELECT my_package.my_function(tab1.col1) answer FROM tab1...

I hope this helps - it does introduce a lot of concepts in one go...

Regards,

Jason Judge Received on Thu Oct 21 1999 - 17:56:16 CDT