Compare char value without knowing the case

  
SQL> create table emp
  2  ( empno      NUMBER(4)    constraint E_PK primary key
  3  , ename      VARCHAR2(8)
  4  , init       VARCHAR2(5)
  5  , job        VARCHAR2(8)
  6  , mgr        NUMBER(4)
  7  , bdate      DATE
  8  , sal       NUMBER(6,2)
  9  , comm       NUMBER(6,2)
 10  , deptno     NUMBER(2)    default 10
 11  ) ;

Table created.

SQL> insert into emp values(1,'Tom','N',   'Coder', 13,date '1965-12-17',  800 , NULL,  20);

1 row created.

SQL> insert into emp values(2,'Jack','JAM', 'Tester',6,date '1961-02-20',  1600, 300,   30);

1 row created.

SQL> insert into emp values(3,'Wil','TF' ,  'Tester',6,date '1962-02-22',  1250, 500,   30);

1 row created.

SQL> insert into emp values(4,'Jane','JM',  'Designer', 9,date '1967-04-02',  2975, NULL,  20);

1 row created.

SQL> insert into emp values(5,'Mary','P',  'Tester',6,date '1956-09-28',  1250, 1400,  30);

1 row created.

SQL> insert into emp values(6,'Black','R',   'Designer', 9,date '1963-11-01',  2850, NULL,  30);

1 row created.

SQL> insert into emp values(7,'Chris','AB',  'Designer', 9,date '1965-06-09',  2450, NULL,  10);

1 row created.

SQL> insert into emp values(8,'Smart','SCJ', 'Coder', 4,date '1959-11-26',  3000, NULL,  20);

1 row created.

SQL> insert into emp values(9,'Peter','CC',   'Designer',NULL,date '1952-11-17',  5000, NULL,  10);

1 row created.

SQL> insert into emp values(10,'Take','JJ', 'Tester',6,date '1968-09-28',  1500, 0,     30);

1 row created.

SQL> insert into emp values(11,'Ana','AA',  'Coder', 8,date '1966-12-30',  1100, NULL,  20);

1 row created.

SQL> insert into emp values(12,'Jane','R',   'Manager',   6,date '1969-12-03',  800 , NULL,  30);

1 row created.

SQL> insert into emp values(13,'Fake','MG',   'Coder', 4,date '1959-02-13',  3000, NULL,  20);

1 row created.

SQL> insert into emp values(14,'Mike','TJA','Manager',   7,date '1962-01-23',  1300, NULL,  10);

1 row created.

SQL>
SQL> select lower(job), initcap(ename)
  2  from   emp
  3  where  upper(job) = 'Tester'
  4  order  by length(ename);

no rows selected

SQL>
SQL> drop table emp;

Table dropped.

Related examples in the same category

1.	The CHAR data type is used for storing fixed-length character strings.
2.	char type with 50 characters
3.	Define varchar2 type variable
4.	Comparison of CHAR with VARCHAR2
5.	Define and use char value
6.	set column width by setting char number
7.	CHAR() and VARCHAR2()
8.	'' represents a zero length character string BUT NOT a null value
9.	No equality comparison between two '' strings
10.	Convert string to clob
11.	char type and varchar2 type
12.	Memory allocation differences between the CHAR and VARCHAR2 datatypes

Compare char value without knowing the case : CHAR « Data Type « Oracle PL / SQL

Related examples in the same category