I have been trying to write a query where I need to find a salary which does not end with ZERO

My Query :

select salary from employees where REGEXP_LIKE (Salary, '^(1|2|3|4|5|6|7|8|9)$');

CodePudding user response：

I would just use the modulus here:

SELECT salary
FROM employees
WHERE MOD(salary, 10) != 0;

By definition, the modulus 10 returns the single (final) digit in the salary. Going this route avoids a costly conversion of the salary to a string, before another costly regex check.

CodePudding user response：

In addition to Tim Biegeleisen anwser, if your sailary is non integer type:

1. String. Here you just can take the last character of a string:

   substr(salary, -1, 1) != '0'

2. Decimal, double etc:

   Here you just can just cast it to integer, if you mean only last digit of int part, for example 4 from 314.58:

   MOD(FLOOR(Salary), 10) != 0

   If you means digit of fractional part of the number, you should multiply Sailary for sertain x10/100/1000 number (according to number of digits after point), and then convert it to int to check division remainder:

   MOD(FLOOR(Salary * 100), 10) != 0

P.S. Edited by @alex-poole comment