Oracle sql function question

Question

How to display the details of employee whose name contains the same characters at the start and end position of their name?

Answer 1

There are two ways to do this using SUBSTR() to identify a portion of the ENAME. The more orthodox approach works on the basis that passing a negative value as the offset counts from the end of the string:

SQL> select ename
  2  from emp
  3  where substr(ename,1,1) = substr(ename,-1,1)
  4  /

ENAME
----------
TROMBONIST

SQL>

Just for grins, I include the second approach which uses the undocumented REVERSE() function:

SQL> select ename, reverse(ename)
  2  from emp
  3  where substr(ename,1,1) = substr(reverse(ename),1,1)
  4  /

ENAME      REVERSE(EN
---------- ----------
TROMBONIST TSINOBMORT

SQL>

In 10g and higher we can also be solve this with regular expressions:

SQL> select ename
  2  from emp
  3  where regexp_substr(ename,'^.') = regexp_substr(ename,'.$')
  4  /

ENAME
----------
TROMBONIST

SQL>