How to show a query statement where it ends with a certain letter?-CodePudding

I have this problem in my query statement in which I have to exclude all the salesperson whose first names ends on y (Lily, Maddy), and cannot use the keyword 'LIKE'.

Here is my query and output so far, this is what it should look like even after adding the exclusion of the names that end in the letter y:

Expected output but without the exclusion

When adding commands such as "REGEXP_LIKE":

enter image description here

TL;DR I need to find a way to exclude all names that end with the letter y in my table without getting rid of the, in Emp Name and without using the keyword "LIKE"

CodePudding user response：

If you want to avoid everyone who's first name ends in Y, then use this:

    where substr(first_name, -1) <> 'y'

CodePudding user response：

As you didn't post any sample data, I made some on my own.

SQL> with
  2  employees (employee_id, first_name, last_name) as
  3    (select 1, 'Freya', 'Gomez'  from dual union all
  4     select 2, null   , null     from dual union all
  5     select 9, 'Lily' , 'Marlen' from dual
  6    ),
  7  orders (salesman_id, order_id) as
  8    (select 2, 100 from dual union all
  9     select 9, 900 from dual
 10    )
 11  select e.employee_id,
 12         e.first_name ||', '|| e.last_name full_name,
 13         o.order_id
 14  from employees e left join orders o on o.salesman_id = e.employee_id
 15  order by e.employee_id;

EMPLOYEE_ID FULL_NAME       ORDER_ID
----------- ------------- ----------
          1 Freya, Gomez
          2 ,                    100
          9 Lily, Marlen         900   --> you want to get rid of this row
                                       --  because Lily ends with a "y"
SQL>

If you add a WHERE clause (line #15), you'll get wrong result because - as you said - you want to keep the comma row:

<snip>
 11  select e.employee_id,
 12         e.first_name ||', '|| e.last_name full_name,
 13         o.order_id
 14  from employees e left join orders o on o.salesman_id = e.employee_id
 15  where substr(e.first_name, -1) <> 'y'
 16  order by e.employee_id;

EMPLOYEE_ID FULL_NAME       ORDER_ID
----------- ------------- ----------
          1 Freya, Gomez

SQL>

NVL comes to rescue! See line #15:

<snip>
 11  select e.employee_id,
 12         e.first_name ||', '|| e.last_name full_name,
 13         o.order_id
 14  from employees e left join orders o on o.salesman_id = e.employee_id
 15  where nvl(substr(e.first_name, -1), ',') <> 'y'                         --> this
 16  order by e.employee_id;

EMPLOYEE_ID FULL_NAME       ORDER_ID
----------- ------------- ----------
          1 Freya, Gomez
          2 ,                    100

SQL>