question

Ruud56 avatar image
Ruud56 asked

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?

oracleupdatestringreplace
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Squirrel 1 avatar image
Squirrel 1 answered

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

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jim 4 avatar image
Jim 4 answered

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

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jay D avatar image
Jay D answered

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            
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

linlasj avatar image
linlasj answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

linlasj avatar image
linlasj answered
No, you dont.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.