Oracle PL SQL string compare issue

Question

I have the following Oracle PL SQL codes that may be rusty from you guys perspective     DECLARE  str1  varchar2 4000    str2  varchar2 4000    BEGIN    str1         str2   sdd      IF str1 lt  gt str2  THEN     dbms output put line  The two strings is not equal       END IF   END       This is very obvious that two strings str1 and str2 are not equal  but why  The two strings are not equal  was not printed out  Do Oracle have another common method to compare two string

User · Answer

To the first question   Probably the message wasn t print out because you have the output turned off  Use these commands to turn it back on   set serveroutput on exec dbms output enable 1000000     On the second question   My PLSQL is quite rusty so I can t give you a full snippet  but you ll need to loop over the result set of the SQL query and CONCAT all the strings together

User · Answer

I ve created a stored function for this text comparison purpose   CREATE OR REPLACE FUNCTION TextCompare vOperand1 IN VARCHAR2  vOperator IN VARCHAR2  vOperand2 IN VARCHAR2  RETURN NUMBER DETERMINISTIC AS BEGIN   IF vOperator       THEN     RETURN CASE WHEN vOperand1   vOperand2 OR vOperand1 IS NULL AND vOperand2 IS NULL THEN 1 ELSE 0 END    ELSIF vOperator     lt  gt   THEN     RETURN CASE WHEN vOperand1  lt  gt  vOperand2 OR  vOperand1 IS NULL   lt  gt   vOperand2 IS NULL  THEN 1 ELSE 0 END    ELSIF vOperator     lt    THEN     RETURN CASE WHEN vOperand1  lt   vOperand2 OR vOperand1 IS NULL THEN 1 ELSE 0 END    ELSIF vOperator     gt    THEN     RETURN CASE WHEN vOperand1  gt   vOperand2 OR vOperand2 IS NULL THEN 1 ELSE 0 END    ELSIF vOperator     lt   THEN     RETURN CASE WHEN vOperand1  lt  vOperand2 OR vOperand1 IS NULL AND vOperand2 IS NOT NULL THEN 1 ELSE 0 END    ELSIF vOperator     gt   THEN     RETURN CASE WHEN vOperand1  gt  vOperand2 OR vOperand1 IS NOT NULL AND vOperand2 IS NULL THEN 1 ELSE 0 END    ELSIF vOperator    LIKE  THEN     RETURN CASE WHEN vOperand1 LIKE vOperand2 OR vOperand1 IS NULL AND vOperand2 IS NULL THEN 1 ELSE 0 END    ELSIF vOperator    NOT LIKE  THEN     RETURN CASE WHEN vOperand1 NOT LIKE vOperand2 OR  vOperand1 IS NULL   lt  gt   vOperand2 IS NULL  THEN 1 ELSE 0 END    ELSE     RAISE VALUE ERROR    END IF  END    In example   SELECT   FROM MyTable WHERE TextCompare MyTable a    gt     MyTable b    1

User · Answer

I compare strings using   and not  lt  gt   I ve found out that in this context   seems to work in more reasonable fashion than  lt  gt   I have specified that two empty  or NULL  strings are equal  The real implementation returns PL SQL boolean  but here I changed that to pls integer  0 is false and 1 is true  to be able easily demonstrate the function   create or replace function is equal a in varchar2  b in varchar2  return pls integer as begin   if a is null and b is null then     return 1    end if     if a   b then     return 1    end if     return 0  end    show errors  begin      Prints 0      dbms output put line is equal  AAA    BBB       dbms output put line is equal  AAA   null      dbms output put line is equal null   BBB       dbms output put line is equal  AAA           dbms output put line is equal      BBB           Prints 1      dbms output put line is equal null  null      dbms output put line is equal null          dbms output put line is equal             dbms output put line is equal  AAA    AAA     end

User · Answer

As Phil noted  the empty string is treated as a NULL  and NULL is not equal or unequal to anything  If you expect empty strings or NULLs  you ll need to handle those with NVL      DECLARE  str1  varchar2 4000    str2  varchar2 4000    BEGIN    str1         str2   sdd   -- Provide an alternate null value that does not exist in your data     IF NVL str1  X      NVL str2  Y    THEN     dbms output put line  The two strings are not equal       END IF   END         Concerning null comparisons   According to the Oracle 12c documentation on NULLS  null comparisons using IS NULL or IS NOT NULL do evaluate to TRUE or FALSE  However  all other comparisons evaluate to UNKNOWN  not FALSE  The documentation further states      A condition that evaluates to UNKNOWN acts almost like FALSE  For example  a SELECT statement with a condition in the WHERE clause that evaluates to UNKNOWN returns no rows  However  a condition evaluating to UNKNOWN differs from FALSE in that further operations on an UNKNOWN condition evaluation will evaluate to UNKNOWN  Thus  NOT FALSE evaluates to TRUE  but NOT UNKNOWN evaluates to UNKNOWN    A reference table is provided by Oracle   Condition       Value of A    Evaluation ---------------------------------------- a IS NULL       10            FALSE a IS NOT NULL   10            TRUE         a IS NULL       NULL          TRUE a IS NOT NULL   NULL          FALSE a   NULL        10            UNKNOWN a    NULL       10            UNKNOWN a   NULL        NULL          UNKNOWN a    NULL       NULL          UNKNOWN a   10          NULL          UNKNOWN a    10         NULL          UNKNOWN   I also learned that we should not write PL SQL assuming empty strings will always evaluate as NULL      Oracle Database currently treats a character value with a length of zero as null  However  this may not continue to be true in future releases  and Oracle recommends that you do not treat empty strings the same as nulls

User · Answer

To fix the core question   how should I detect that these two variables don t have the same value when one of them is null    I don t like the approach of nvl my column   some value that will never  ever  ever appear in the data and I can be absolutely sure of that   because you can t always guarantee that a value won t appear    especially with NUMBERs   I have used the following   if  str1 is null   lt  gt   str2 is null  or str1  lt  gt  str2 then   dbms output put line  not equal    end if    Disclaimer  I am not an Oracle wizard and I came up with this one myself and have not seen it elsewhere  so there may be some subtle reason why it s a bad idea  But it does avoid the trap mentioned by APC  that comparing a null to something else gives neither TRUE nor FALSE but NULL  Because the clauses  str1 is null  will always return TRUE or FALSE  never null    Note that PL SQL performs short-circuit evaluation  as noted here

User · Answer

Let s fill in the gaps in your code  by adding the other branches in the logic  and see what happens   SQL gt  DECLARE   2   str1  varchar2 4000     3   str2  varchar2 4000     4  BEGIN   5     str1        6     str2   sdd     7     IF str1 lt  gt str2  THEN   8      dbms output put line  The two strings is not equal      9     ELSIF  str1 str2  THEN  10      dbms output put line  The two strings are the same     11     ELSE  12      dbms output put line  Who knows      13     END IF   14   END   15    Who knows   PL SQL procedure successfully completed   SQL gt    So the two strings are neither the same nor are they not the same   Huh     It comes down to this  Oracle treats an empty string as a NULL   If we attempt to compare a NULL and another string the outcome is not TRUE nor FALSE  it is NULL   This remains the case even if the other string is also a NULL

User · Answer

Only change the line  str1      to str1

[sql] Oracle PL/SQL string compare issue

Examples related to sql

Examples related to oracle

Examples related to plsql

Examples related to oracle10g

Examples related to oracle11g