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?
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
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
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
