Home » SQL & PL/SQL » SQL & PL/SQL » replace the character based on the positions (merged)
replace the character based on the positions (merged) [message #412202] Wed, 08 July 2009 03:57 Go to next message
sundarfaq
Messages: 235
Registered: October 2007
Location: Chennai
Senior Member
Hi,

I want to replace a character based on the positions.i want to replace the 'l' with 3 for the second occurence of 'l 'for 'michaell'. i tried to replace function. but it replaces all l occurance.

input

Michaell

i want to replace l with 3 for first occurance. the output is

michae3l

how can resolve this issue?
Re: replace the character based on the positions [message #412210 is a reply to message #412202] Wed, 08 July 2009 04:12 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
use Instr to find the first occurence and replace it with Replace
Re: replace the character based on the positions [message #412212 is a reply to message #412202] Wed, 08 July 2009 04:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use INSTR and SUBSTR, adn maybe REGXP_REPLACE.

Regards
Michel
Re: replace the character based on the positions (merged) [message #412353 is a reply to message #412202] Wed, 08 July 2009 19:28 Go to previous messageGo to next message
baekyasi
Messages: 4
Registered: July 2009
Location: Republic of Korea
Junior Member
Hi.
I think...


SELECT CASE WHEN instr('Michaell', 'i', 1) > 0 THEN
substr('Michaell', 1, instr('Michaell', 'l', 1)-1)
|| '3'
|| substr('Michaell', instr('Michaell', 'l', 1)+1, length('Michaell'))
ELSE
'0'
END
FROM dual
;


- B.rgds / Eylee
Re: replace the character based on the positions (merged) [message #412354 is a reply to message #412202] Wed, 08 July 2009 19:47 Go to previous message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
when any klunky code meet kludge "requirements".
sort of like Garbage In, Garbage Out (GIGO)

  1  SELECT CASE
  2	      WHEN Instr('Michaell','i',1) > 0 THEN Substr('Michaell',1,Instr('Michaell','l',1) - 1)
  3						    ||'3'
  4						    ||Substr('Michaell',Instr('Michaell','l',1) + 1,Length('Michaell'))
  5	      ELSE '0'
  6	    END
  7* FROM   dual
SQL> /

CASEWHEN
--------
Michae3l

SQL> select decode('Michaell','Michaell','Michae3l') from dual;

DECODE('
--------
Michae3l

Previous Topic: Help needed with update
Next Topic: Problem in spooling a file.
Goto Forum:
  


Current Time: Mon Dec 05 19:14:19 CST 2016

Total time taken to generate the page: 0.24791 seconds