x

SQL statement for replace/update a stringvalue in an Oracle table

I have to update an Oracle table which contains in a column a stringvalue like p:\db1\photos\streetname\picture.jpg. This stringvalue has to be changed in the following: p:\db1\photos\cityname\streetname\picture.jpg. The new value to be added in the string is cityname. This has to be done for over 600 records. How can I do this?

more ▼

asked Jun 15 '10 at 06:09 AM in Default

Ruud56 gravatar image

Ruud56
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

we can help you better if you are using SQL Server. Any chance of converting to MS SQL Server ? Or maybe you should try to post your oracle related question over at dbforums.com. SQLTeam specialize in Microsoft SQL Server

more ▼

answered Jun 15 '10 at 09:41 AM

Squirrel 1 gravatar image

Squirrel 1
1.6k 1 3

(comments are locked)
10|1200 characters needed characters left

are you just trying to replace one string with another in all 600+ records ? the string does or does not change ?

in other words, replace 'p:\db1\photos\streetname\picture.jpg' with 'p:\db1\photos\cityname\streetname\picture.jpg'

in all records

Jim

more ▼

answered Jun 15 '10 at 02:42 PM

Jim 4 gravatar image

Jim 4
2 1 1 1

(comments are locked)
10|1200 characters needed characters left

I'm not an expert in Oracle, but I do know SSIS. Use an OLEDB Source Component, retrieve your data, pull it down the pipe to a Derived Column Compenent, Use an Expression to update the column and push to an OLEDB Destination Component. I suppose this would work. I'm sure there are other possible solutions. If you don't have access to SSIS, then I would suggest creating a script that uses string manipulation to transform the data.

In TSQL you could possibly do this...

CREATE TABLE #StringReplace            
(            
      RecId     INT          NOT NULL IDENTITY            
    , FileLoc   VARCHAR(100) NOT NULL            
)            
            
INSERT INTO #StringReplace  VALUES             
                    ('p:\db1\photos\streetname\picture.jpg')            
                   ,('p:\db1\photos\streetname\picture1.jpg')            
                   ,('p:\db1\photos\streetname\picture2.jpg')            
                   ,('p:\db1\photos\streetname\picture3.jpg')            
                   -- want one record that is already correct            
                   ,('p:\db1\photos\cityname\streetname\picture4.jpg')            
            
            
-- See what the records look like before applying changes            
SELECT RecId, FileLoc AS BeforeChange FROM #StringReplace            
            
            
DECLARE @strSearch  CHAR(8)            
SET @strSearch = 'cityname'            
            
BEGIN            
            
UPDATE #StringReplace            
SET FileLoc =             
    CASE             
    WHEN CHARINDEX('\cityname\',FileLoc,1) = 0             
                THEN LTRIM(SUBSTRING(FileLoc,1,CHARINDEX('\streetname',FileLoc,1)))             
                + @strSearch              
                + (SUBSTRING(FileLoc,CHARINDEX('\streetname',FileLoc,1),99))            
    ELSE FileLoc            
    END            
END            
GO            
            
SET NOCOUNT ON            
-- See what the records look like after the changes are applied            
SELECT RecId, FileLoc AS AfterChange FROM #StringReplace            
            
DROP TABLE #StringReplace            
GO              

This will return the following result sets...

RecId BeforeChange
----------- ---------------------------------------------------------------------------
1 p:\db1\photos\streetname\picture.jpg
2 p:\db1\photos\streetname\picture1.jpg
3 p:\db1\photos\streetname\picture2.jpg
4 p:\db1\photos\streetname\picture3.jpg
5 p:\db1\photos\cityname\streetname\picture4.jpg RecId AfterChange
----------- ---------------------------------------------------------------------------
1 p:\db1\photos\cityname\streetname\picture.jpg
2 p:\db1\photos\cityname\streetname\picture1.jpg
3 p:\db1\photos\cityname\streetname\picture2.jpg
4 p:\db1\photos\cityname\streetname\picture3.jpg
5 p:\db1\photos\cityname\streetname\picture4.jpg
more ▼

answered Jun 15 '10 at 09:47 PM

Jay D gravatar image

Jay D
128 1 1 3

SUBSTRING AND CHARINDEX are easy to use and powerful once understood. See BOL at http://msdn.microsoft.com/en-us/library/ms186323.aspx for more information.
Jun 15 '10 at 10:00 PM Jay D
(comments are locked)
10|1200 characters needed characters left

Please use replace in Oracle, can be done in one update statement.

http://www.techonthenet.com/oracle/functions/replace.php

Probably you need substring as well.
more ▼

answered Mar 22 '11 at 04:40 PM

linlasj gravatar image

linlasj
0 1

(comments are locked)
10|1200 characters needed characters left
No, you dont.
more ▼

answered Mar 22 '11 at 04:40 PM

linlasj gravatar image

linlasj
0 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x376
x128
x33
x18

asked: Jun 15 '10 at 06:09 AM

Seen: 7915 times

Last Updated: Jun 15 '10 at 06:09 AM