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

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.

No, you dont.

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