Home » SQL & PL/SQL » SQL & PL/SQL » Replace a String
Replace a String [message #325378] Fri, 06 June 2008 01:14 Go to next message
M.Shakeel Azeem
Messages: 226
Registered: September 2006
Senior Member
Dear All,

i have a table with the following data

DEVICE_ID   JAR_FILE	        
  
22	   O2-STEALTH-Genie	 
15	   Sony-Genie-W810i-Genie	
16	   BlackGenie8310Genie	
17	   BlackBerry8800Genie	
18	   LG-KGenie300-Genie	
19	   Nokia-6020-Genie	
20	   Nokia-6500-Genie	
14	   Genie-SGH-E900-Genie	
21	   HTC-P3450-Genie	


I want to replace the string Genie with iPOS but i just want to replace Genie at the end of Columns
e.g
i want to update value Sony-Genie-W810i-Genie as Sony-Genie-W810i-iPOS
we can use replace function but replace will replace all Genie substrings with iPOS
if i use replace function as replace(JAR_FILE,'Genie','iPOS')
then it will replace the value Sony-Genie-W810i-Genie as Sony-iPOS-W810i-iPOS but i want to replace the value Sony-Genie-W810i-Genie as Sony-Genie-W810i-iPOS

Please suggest!
thanx in advance
Re: Replace a String [message #325381 is a reply to message #325378] Fri, 06 June 2008 01:29 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
you can use a WHERE condition
specifying LIKE.
eg: LIKE '%Genie'
Re: Replace a String [message #325384 is a reply to message #325378] Fri, 06 June 2008 01:45 Go to previous messageGo to next message
M.Shakeel Azeem
Messages: 226
Registered: September 2006
Senior Member
No it'll not resolve my problem
According to your suggestions if i use

update test
set jar_file=(jar_file,'Genie','iPOS')
where jar_file like '%Genie'

then the value Sony-Genie-W810i-Genie will be updated as Sony-iPOS-W810i-iPOS
but as i mentioned in previous post i want to update the value
Sony-Genie-W810i-Genie will be updated as Sony-Genie-W810i-iPOS

thanx

Re: Replace a String [message #325386 is a reply to message #325384] Fri, 06 June 2008 01:52 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
use instr function to get second position of G character

SELECT SUBSTR('Sony-Genie-W810i-Genie',1,INSTR('Sony-Genie-W810i-Genie','G',1,2)-1)||'iPOS'  FROM dual
Re: Replace a String [message #325387 is a reply to message #325384] Fri, 06 June 2008 01:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you're using 10g, you should look at the REGEXP_REPLACE command.
Re: Replace a String [message #325388 is a reply to message #325384] Fri, 06 June 2008 02:01 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
WITH TEST AS (SELECT 'SONYTESTSONY' A, 1 ID FROM DUAL
	 	  	 UNION
			 SELECT 'SONYTEST' A, 2 ID FROM DUAL
			 UNION
			 SELECT 'SONYTEST' A, 3 ID FROM DUAL
			 UNION
			 SELECT 'SONYTESTSONY' A, 4 ID FROM DUAL
)
SELECT * FROM TEST
WHERE A LIKE '%SONY' 	 
	 	  	 	
	
Re: Replace a String [message #325391 is a reply to message #325388] Fri, 06 June 2008 02:03 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
oops!

now I got your problem.

As JRowbottom said, use regexp_replace.

sorry once again.
Re: Replace a String [message #325392 is a reply to message #325388] Fri, 06 June 2008 02:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sarwagya wrote on Fri, 06 June 2008 09:01
WITH TEST AS (SELECT 'SONYTESTSONY' A, 1 ID FROM DUAL
	 	  	 UNION
			 SELECT 'SONYTEST' A, 2 ID FROM DUAL
			 UNION
			 SELECT 'SONYTEST' A, 3 ID FROM DUAL
			 UNION
			 SELECT 'SONYTESTSONY' A, 4 ID FROM DUAL
)
SELECT * FROM TEST
WHERE A LIKE '%SONY' 	 


How this will help to do the replace? The problem is not in the select but in the update.

Regards
Michel


Re: Replace a String [message #325396 is a reply to message #325388] Fri, 06 June 2008 02:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Here we go - REGEXP_REPLACE (it took me a minute or two to find where I'd put my 10 database.)
  1  WITH TEST AS (SELECT 'SONYTESTSONY' A, 1 ID FROM DUAL UNION ALL
  2                SELECT 'SONYTEST'     A, 2 ID FROM DUAL UNION ALL
  3                SELECT 'TESTSONY'     A, 3 ID FROM DUAL)
  4  SELECT a,regexp_replace(a,'SONY$','IPOD') new_a
  5* FROM TEST
SQL> /

A
------------
NEW_A
---------------------------------------------------------------------------
SONYTESTSONY
SONYTESTIPOD

SONYTEST
SONYTEST

TESTSONY
TESTIPOD
Re: Replace a String [message #325420 is a reply to message #325378] Fri, 06 June 2008 02:57 Go to previous message
M.Shakeel Azeem
Messages: 226
Registered: September 2006
Senior Member
Dear All,

My problem has been resolved by using regexp_replace
look at both select and update statements

select jar_file ,REGEXP_REPLACE(jar_file, 'Genie$', 'iPOS' )
from  device_jar

update DEVICE_JAR
set jar_file=REGEXP_REPLACE(jar_file, 'Genie$', 'iPOS' );


thanx
Previous Topic: EXTRACT DATA
Next Topic: Index's are not properly using
Goto Forum:
  


Current Time: Thu Dec 08 04:30:50 CST 2016

Total time taken to generate the page: 0.13999 seconds