Replace part of string in table column

Question

Using Remove part of string in table as an example, I want to change part of my string in my database column with a different string.

Ex:

Database says E:\websites\nas\globe.png , E:\websites\nas\apple.png and etc

I want it to say \\nas\globe.png, \\nas\apple.png,

Only part I want to replace is the E:\websites\ not the rest of the string

How do I do this?

So far I have:

SELECT file_name,  
REPLACE(file_name,'E:\websites\','\\nas\')   
FROM t_class;

I just referenced http://nntp-archive.sybase.com/nntp-archive/action/article/%3C348_1744DC78C1045E920059DE7F85256A8B.0037D71C85256A8B@webforums%3E

and used:

SELECT REPLACE('E:\websites\web\Class\Main_Image\','E:\websites\web\Class\Main_Image\','\\nas\class_s\Main_Image\') "Changes" 
FROM DUAL;

but once again it wouldn't change O.o

Gordon Linoff · Accepted Answer · 2015-08-05 18:52:12Z

1

In Oracle, you may need to double up on the back slashes:

SELECT file_name,  
       REPLACE(file_name,'E:\\websites\\', '\\\\nas\\')   
FROM t_class;

answered Aug 5, 2015 at 18:52

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

narue1992 Over a year ago

When I run this in InfoMaker, there is no errors similarly to my format. However, when I test this and select * from my table the E:\website ones still show up

Gary_W · Accepted Answer · 2015-08-05 19:40:47Z

1

For the fun of it, using regexp_replace:

SQL> with tbl(filename) as (
  2  select 'E:\websites\nas\globe.png' from dual
  3  union
  4  select 'E:\websites\nas\apple.png' from dual
  5  )
  6  select filename, regexp_replace(filename, 'E:\\websites', '\\') edited
  7  from tbl;

FILENAME                  EDITED
------------------------- --------------------
E:\websites\nas\apple.png \\nas\apple.png
E:\websites\nas\globe.png \\nas\globe.png

SQL>

answered Aug 5, 2015 at 19:40

Gary_W

10.4k1 gold badge26 silver badges42 bronze badges

3 Comments

narue1992 Over a year ago

It looks like yours isn't based on a long listing but rather a specific listing the user looks for... my example there are like 50 fields for file_name column ... all that have a different "".png file name at the end of the string aka I need it too look at all fields and replace the E:\websites...etc I tried doing:

with t_class(file_name) as (   select 'E:\websites\web\Class\Main_Image' from dual   )   select file_name, regexp_replace(file_name, 'E:\websites\web\Class\Main_Image', '\\nas\class_s\Main_Image\') edited   from t_class;

and still it wasn't replace ? makes no sense why

Gary_W Over a year ago

As Gordon said you need to escape the slashes:

with t_class(file_name) as  ( select 'E:\websites\web\Class\Main_Image' from dual  )  select file_name, regexp_replace(file_name, 'E:\\websites\\web\\Class\\Main_Image', '\\\nas\class_s\Main_Image\') edited from t_class;

narue1992 Over a year ago

Even with the extra \\ it doesn't work; however, I submitted my answer above. I found out I needed the update part for my coding example

Community · Accepted Answer · 2017-05-23 12:14:37Z

1

I found a reference at how to replace string values of column values by SQL Stored Procedure

by doing the following:

UPDATE t_class SET file_name = 

REPLACE

(file_name, 'E:\websites\web\Class\Main_Image\No_Image_Available.png', '\\nas\class_s\Main_Image\No_Image_Available.png');

so only difference is the update and = sign

edited May 23, 2017 at 12:14

CommunityBot

11 silver badge

answered Aug 6, 2015 at 11:58

narue1992

1,2231 gold badge16 silver badges41 bronze badges

Collectives™ on Stack Overflow

Replace part of string in table column

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related