An interview question that checks knowledge of Oracle SQL character functions.

Level: Beginner

Question: Find all employee names beginning with ‘S’ not using LIKE operator.

Method #1: INSTR function

SELECT ename
FROM emp
WHERE INSTR(ename, 'S')=1
/

--Result:

ENAME
-------
SMITH
SCOTT

Workaround/Method #2: Range Test

SELECT ename
FROM emp
WHERE ename>='S' AND ename<'T'
/

--Result:

ENAME
-------
SMITH
SCOTT

Workaround/Method #3: SUBSTR function

SELECT ename
FROM emp
WHERE SUBSTR(ename, 1, 1) = 'S'
/

--Result:

ENAME
-------
SMITH
SCOTT

Workaround/Method #4: ASCII function

SELECT ename
FROM emp
WHERE ASCII(ename)=ASCII('S')
/

--Result:

ENAME
-------
SMITH
SCOTT

Workaround/Method #5: SUBSTR functions

SELECT ename
FROM emp
WHERE 'S' || SUBSTR(ename, 2) = ename
/

--Result:

ENAME
-------
SMITH
SCOTT

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.