Solved

SQL PLUS FUNCTIONS

Posted on 2003-10-29
6
2,352 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:jheff
6 Comments
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 9640721
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
0
 
LVL 1

Author Comment

by:jheff
ID: 9640741
catchmeifuwant,

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

thanks.
0
 
LVL 2

Expert Comment

by:racher
ID: 9640764
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
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 9640925
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
0
 
LVL 7

Accepted Solution

by:
Bigfam5 earned 100 total points
ID: 9641053
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;



0
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 9641088
:-) Thanks for pointing it out...jumbled the ename and substr !!!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  â€¦
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to recover a database from a user managed backup

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question