SQL query with "LIKE"

Question

I'm trying to make a query and I don't understand why it doesn't work for me. I would appreciate it if someone could give me a hand

SELECT TEMA 
FROM LIBRERIAS 
WHERE TEMA LIKE '%IA';

This query does not return data even if it has data that matches such as "GEOMETRIA"

Answer 1

To eliminate space you can trim TEMA and to include both upper case and lower case matches you can use upper() like below:

SELECT TEMA 
FROM LIBRERIAS 
WHERE upper(trim(TEMA)) LIKE '%IA';

Answer 2

Taking care of possible additional characters as well as capital or small leters if last two letters are realy the condition for selecting rows then you could consider using SubStr() function (probably more efficient) to do the same:

WITH
  LIBRERIAS (ID, TEMA, NOTE) AS
      ( Select 1, 'Geometria', 'ending in small letters' From Dual Union All
        Select 2, 'GEOMETRIA.', 'ending with dot' From Dual Union All
        Select 3, 'GEOMETRIA ', 'ending with blank' From Dual Union All
        Select 4, 'GEOMETRIA', 'This is the one' From Dual 
      )
/*    S a m p l e    D a t a :
        ID TEMA       NOTE                   
---------- ---------- -----------------------
         1 Geometria  ending in small letters
         2 GEOMETRIA. ending with dot        
         3 GEOMETRIA  ending with blank      
         4 GEOMETRIA  This is the one           */

--  M a i n    S Q L :
SELECT ID, TEMA, NOTE
FROM LIBRERIAS 
WHERE SubStr(TRIM(Upper(TEMA)), -2) = 'IA';
/*
        ID TEMA       NOTE                   
---------- ---------- -----------------------
         1 Geometria  ending in small letters
         3 GEOMETRIA  ending with blank      
         4 GEOMETRIA  This is the one         */

In the above resultset there is no row with ID = 2 cause its TEMA ends with a dot which was not handled in code...