Oracle PL/SQL - PL SQL Operator Character Comparisons

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Operator
  4. PL/SQL Comparison Operators

Introduction

By default, one character is greater than another if its binary value is larger.

For example, this expression is true: 

'y' > 'r'

Strings are compared character by character. For example, this expression is true: 

'My' > 'Mr'

If parameter NLS_COMP=ANSI, string comparisons use the collating sequence identified by the NLS_SORT initialization parameter.

By changing the value of the NLS_SORT parameter, you can perform comparisons, for example, case-insensitive and accent-insensitive.

A case-insensitive comparison treats corresponding uppercase and lowercase letters as the same letter. For example, these expressions are true: 

'a' = 'A' 
'Alpha' = 'ALPHA'

To make comparisons case-insensitive, append _CI to the value of the NLS_SORT parameter, for example, BINARY_CI or XGERMAN_CI.

To make comparisons both case-insensitive and accent-insensitive, append _AI to the value of the NLS_SORT parameter, for example, BINARY_AI or FRENCH_M_AI.

Demo

SQL>
SQL> CREATE OR REPLACE PROCEDURE print_boolean (
  2    b_name   VARCHAR2,-- from  w  w w.j av a 2  s .c om
  3    b_value  BOOLEAN
  4   ) IS
  5  BEGIN
  6    IF b_value IS NULL THEN
  7      DBMS_OUTPUT.PUT_LINE (b_name || ' = NULL');
  8    ELSIF b_value = TRUE THEN
  9      DBMS_OUTPUT.PUT_LINE (b_name || ' = TRUE');
 10    ELSE
 11      DBMS_OUTPUT.PUT_LINE (b_name || ' = FALSE');
 12    END IF;
 13  END;
 14  /

Procedure created.

SQL> BEGIN
  2    print_boolean ('a < y', 'a'< 'y');
  3
  4
  5  END;
  6  /
a < y = TRUE

PL/SQL procedure successfully completed.

SQL>

Related Topic