How to display the details of employee whose name contains the same characters at the start and end position of their name?
-
Smells like homework ...Rob van Wijk– Rob van Wijk2010-06-21 08:12:57 +00:00Commented Jun 21, 2010 at 8:12
-
homework hint: lookup the instr function and see if you can use that somehow in your query or maybe do something clever with regexp.Rene– Rene2010-06-21 08:45:39 +00:00Commented Jun 21, 2010 at 8:45
Add a comment
|
1 Answer
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>