SEO

#1 (**permalink**) 02-29-2008, 03:37 AM

I am trying to transform data inside one of my tables. However, I only
want to change part of the data in a column because the last part is
basically a UniqueID to a file. Is it possible to only change part of
it?

Here is what the column looks like. I have thousands of these.

D:\oldpath\Content\Data\{File1}
D:\oldpath\Content\Previews\Micro\{File1}.jpg
D:\oldpath\Content\Previews\Mini\{File1}.jpg
D:\oldpath\Content\Data\{File2}
D:\oldpath\Content\Previews\Micro\{File2}.jpg
D:\oldpath\Content\Previews\Mini\{File2}.jpg

I tried commands similar to the one below but nothing happened.

update tableinquestion set columninquestion = 'E:\newpath\Data' where
columninquestion like 'D:\oldpath\Content\Data'

Any help would be appreciated.

#2 (**permalink**) 02-29-2008, 03:38 AM

One method:

CREATE TABLE tableinquestion
(
columninquestion varchar(255) NOT NULL
)

INSERT INTO tableinquestion VALUES('D:\oldpath\Content\Data\{File1}')
INSERT INTO tableinquestion
VALUES('D:\oldpath\Content\Previews\Micro\{File1}. jpg')
INSERT INTO tableinquestion
VALUES('D:\oldpath\Content\Previews\Mini\{File1}.j pg')
INSERT INTO tableinquestion VALUES('D:\oldpath\Content\Data\{File2}')
INSERT INTO tableinquestion
VALUES('D:\oldpath\Content\Previews\Micro\{File2}. jpg')
INSERT INTO tableinquestion
VALUES('D:\oldpath\Content\Previews\Mini\{File2}.j pg')
GO

UPDATE tableinquestion
SET columninquestion = 'E:\newpath\Data' +
REVERSE(
SUBSTRING(
REVERSE(
columninquestion), 1, CHARINDEX('\',
REVERSE(columninquestion))))
FROM tableinquestion
WHERE
columninquestion LIKE 'D:\oldpath\Content\Data%'

--
Hope this helps.

Dan Guzman
SQL Server MVP

"C.P." <carmenpuccio@yahoo.com> wrote in message
news:8b8faf60.0407151628.2619c010@posting.google.c om...
> I am trying to transform data inside one of my tables. However, I only
> want to change part of the data in a column because the last part is
> basically a UniqueID to a file. Is it possible to only change part of
> it?
>
> Here is what the column looks like. I have thousands of these.
>
> D:\oldpath\Content\Data\{File1}
> D:\oldpath\Content\Previews\Micro\{File1}.jpg
> D:\oldpath\Content\Previews\Mini\{File1}.jpg
> D:\oldpath\Content\Data\{File2}
> D:\oldpath\Content\Previews\Micro\{File2}.jpg
> D:\oldpath\Content\Previews\Mini\{File2}.jpg
>
> I tried commands similar to the one below but nothing happened.
>
> update tableinquestion set columninquestion = 'E:\newpath\Data' where
> columninquestion like 'D:\oldpath\Content\Data'
>
> Any help would be appreciated.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Update query question