Need an SQL statement for a selective update 1

[njcraig]

I have a table with a field called 'imagepath', in this table is data represented by the two following path types:
1. J:\DIR\DIR\DIR
and
2. \\172.21.2.7\DIR\DIR\DIRWhat I need to do is udpdate the data to represent the following:
\\172.21.10.1\DIR\etc.
In other words don't update the dir paths just update the mapped dirve and old unc to the new unc.

Thanks for any help!

 

[r937]

i'm not so good with terminology like unc and dir path and mapped drive

i have no idea what those are

however, i could probably whip up the sql you're looking for, if you tell me how to find the strings that need replacing, and which ones to replace

for example,

[tt]update yourtable
set imagepath = concat('\\172.21.10.1'
, subtring(imagepath,3))
where imagepath like 'J:\%'[/tt]

rudy

http://r937.com/

SQL Consulting

 

[njcraig]

I want to change J: whatever is after J: I would like to leave alone and replace J: with \\172.21.10.1\mugshots

Thanks again

 

[r937]

okay, you're all set then

and thanks for the star

rudy

http://r937.com/

SQL Consulting

 

[njcraig]

Not really set yet but I am grateful for your help I tried to run that SQL but nothing changed in the Field?

I ran this:
update images
set imagepath = concat('\\172.21.10.1'
, substring(imagepath,3))
where imagepath like 'J:\%';

I don't understand the substring comment?

 

[r937]

oops, my bad, i totally forgot the escape characters

two consecutive backslashes in strings, and a difference escape character in LIKE expressions

[tt]create table images
( id tinyint not null primary key auto_increment
, imagepath varchar(30)
)
;
insert into images (imagepath) values
( 'J:\\DIR\\DIR\\DIR ' )
,( '\\\\172.21.2.7\\DIR\\DIR\\DIR\\ ' )
,( 'J;\\note\\the\\semicolon\\DIR ' )
,( 'J:\\172.21.2.7\\DIR\\DIR\\DIR\\ ' )
;
select * from images
;
1 J:\DIR\DIR\DIR
2 \\172.21.2.7\DIR\DIR\DIR\
3 J;\note\the\semicolon\DIR
4 J:\172.21.2.7\DIR\DIR\DIR\

select * from images
where imagepath like 'j:\\%' escape '^'
;
1 J:\DIR\DIR\DIR
4 J:\172.21.2.7\DIR\DIR\DIR\

update images
set imagepath = concat('\\\\172.21.10.1'
, substring(imagepath,3))
where imagepath like 'J:\%' escape '^'
;
1 \\172.21.10.1\DIR\DIR\DIR
2 \\172.21.2.7\DIR\DIR\DIR\
3 J;\note\the\semicolon\DIR
4 \\172.21.10.1\172.21.2.7\DIR\D [/tt]


rudy

http://r937.com/

SQL Consulting

 

[njcraig]

I am definitely not worthy!
J:\COP153\ and \\172.21.2.7\ddsi\COP100\52\ is what is entered in the field named ImagePath.

What I want changed is \\172.21.10.1\COP153\ and \\172.21.10.1\ddsiCOP100\52


There are diferrent paths in the fields so I only want to change the J: and \\172.21.2.7 and leave the rest intact

 

[r937]

update images
set imagepath = concat('\\\\172.21.10.1'
, substring(imagepath,3))
where imagepath like 'J:\%' escape '^'

update images
set imagepath = concat('\\\\172.21.10.1'
, substring(imagepath,12))
where imagepath like '\\172.21.2.7\^' escape '^'


rudy

http://r937.com/

SQL Consulting