SQL PLUS FUNCTIONS

jheff
jheff used Ask the Experts™
on
I HAVE A TABLE NAMED: EMP
TWO COLUMNS: ENAME AND 1RST_OCCUR.
I MUST FIND THE FIRST OCCURRENCE OF 'L' IN ENAME AND CHANGE IT TO 'X'.
SAMPLE:

ENAME             1RST_OCCUR
--------             ----------------

SMITH                 SMITH
ALLEN                  AXLEN
WARD                  WARD
JONES                 JONES
MARTIN               MARTIN
BLAKE                  BXAKE
CLARK                 CXARK
SCOTT                SCOTT
KING                    KING
TURNER               TURNER
ADAMS                ADAMS
JAMES                 JAMES
FORD                   FORD
MILLER                MIXLER
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
This should do the job...

select ename,
case
when instr(ename,'L') > 0 then
substr(ename,1,instr(ename,'L')-1)||'X'||substr(ename,instr(ename,'L')+1)
else
ename
end
from emp

Author

Commented:
catchmeifuwant,

is it possible to make another command without using case and else?

thanks.

Commented:
If you wanted to change all occurences you would use the functions REPLACE or TRANSLATE
As you only want to change the first occurence then you can use INSTR to find the first occurence and then combine that with the use of SUBSTR to get what you want.

ename = SUBSTR(ename, 1, INSTR(ename, 'L' ) -1 ) || 'X' || SUBSTR(ename,INSTR(ename, 'L' ) +1 )

Graham
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

jheff,


here is an other one using decode...the logic is same as Case When End

select ename,
decode(instr(ename,'L'),0,substr(ename,1,instr(ename,'L')-1)||'X'||substr(ename,instr(ename,'L')+1),ename)
from emp
Commented:
catchmeifuwant you almost had it, except that your returns an extra X in front of the names that do not contain an L

Try this instead

select ename,INSTR(ename, 'L' ),
       decode ( INSTR(ename, 'L'),
                0, ename,
                substr(ename,1, INSTR(ename, 'L' )-1)
                || 'X' ||
                substr(ename, INSTR(ename, 'L' )+1 ) )
 from emp;



:-) Thanks for pointing it out...jumbled the ename and substr !!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial