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, 2010 at 06:09 AM in Default

avatar 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, 2010 at 09:41 AM

avatar image

Squirrel 1
1.6k 1 3 5

(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, 2010 at 02:42 PM

avatar 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, 2010 at 09:47 PM

avatar image

Jay D
128 2 3 6

(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, 2011 at 04:40 PM

avatar image

linlasj
0 1 1

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

No, you dont.

more ▼

answered Mar 22, 2011 at 04:40 PM

avatar image

linlasj
0 1 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.

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:

x424
x161
x41
x30

asked: Jun 15, 2010 at 06:09 AM

Seen: 10150 times

Last Updated: Jun 15, 2010 at 06:09 AM

Copyright 2016 Redgate Software. Privacy Policy