By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,035 Members | 937 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,035 IT Pros & Developers. It's quick & easy.

how to do text replacements

P: n/a
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

Sep 15 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Hi

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" <rc******@cablespeed.com> wrote in message
news:11**********************@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

Sep 15 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.