how to do text replacements

I am trying to do a text replacement to reflect changes where I've
stored data.
A field, backup_archive_filename, contains the url path. I've since
changed the directory structure and wish to change whats stored in the
table.
Example:
\\10.0.12.110\SQLSafe\COGNOS-DEV\2005-08-29_2017m_51s_Diff_COGNOS-DEV_cm.safe
\\10.0.12.110\SQLSafe\TLS-D-AN001\2005-08-29_2041m_11s_Diff_TLS-D-AN001_Northwind.safe

I want to change SQLSafe to SQLSafe\Diff or SQLSafe\Full depending when
there is either %Diff% or %Full% in the string to reflect the change in
the directory.

I wanted to do something like:
update backups_sets
SET backup_archive_filename = <<get first part>>+ 'SQLsafe\Diff' +<<get
last part>> where backup_archive_filename like '%_Diff_%'

I need a function for <<get first part>> like EXTRACTSTR(
backup_archive_filename, '\',3) would return '\\10.0.12.110\SQLSafe'. I
cant find a built in function that can pick apart fields based on a
seperator.
TIA
RobHi

Maybe something like:

UPDATE Mytable
Set URL = STUFF(url, charindex('\SQLSafe\',url),LEN('\SQLSafe\'),CASE WHEN
CHARINDEX('_Diff_',url) > 0 THEN '\SQLSafe\Diff\' ELSE '\SQLSafe\Full\'
END )

You may want to try this out with (this may wrap!):

SELECT STUFF(url, charindex('\SQLSafe\',url),LEN('\SQLSafe\'),CASE WHEN
CHARINDEX('_Diff_',url) > 0 THEN '\SQLSafe\Diff\' ELSE '\SQLSafe\Full\'
END )
FROM ( SELECT
'\\10.0.12.110\SQLSafe\COGNOS-DEV\2005-08-29_2017m_51s_Diff_COGNOS-DEV_cm.safe'
AS url
UNION ALL SELECT
'\\10.0.12.110\SQLSafe\TLS-D-AN001\2005-08-29_2041m_11s_Diff_TLS-D-AN001_Northwind.safe'
UNION ALL SELECT
'\\10.0.12.110\SQLSafe\TLS-D-AN001\2005-08-29_2041m_11s_Full_TLS-D-AN001_Northwind.safe'
) A

John

"rcamarda" <rcamarda@.cablespeed.com> wrote in message
news:1126800395.289507.197650@.g47g2000cwa.googlegr oups.com...
>I am trying to do a text replacement to reflect changes where I've
> stored data.
> A field, backup_archive_filename, contains the url path. I've since
> changed the directory structure and wish to change whats stored in the
> table.
> Example:
> \\10.0.12.110\SQLSafe\COGNOS-DEV\2005-08-29_2017m_51s_Diff_COGNOS-DEV_cm.safe
> \\10.0.12.110\SQLSafe\TLS-D-AN001\2005-08-29_2041m_11s_Diff_TLS-D-AN001_Northwind.safe
> I want to change SQLSafe to SQLSafe\Diff or SQLSafe\Full depending when
> there is either %Diff% or %Full% in the string to reflect the change in
> the directory.
> I wanted to do something like:
> update backups_sets
> SET backup_archive_filename = <<get first part>>+ 'SQLsafe\Diff' +<<get
> last part>> where backup_archive_filename like '%_Diff_%'
>
> I need a function for <<get first part>> like EXTRACTSTR(
> backup_archive_filename, '\',3) would return '\\10.0.12.110\SQLSafe'. I
> cant find a built in function that can pick apart fields based on a
> seperator.
> TIA
> Rob