Solved

SQL Query replace part of string

Posted on 2011-03-09
3
886 Views
Last Modified: 2012-05-11
I am looking for an example of an SQL Query for MS SQL Server which woudl allow me to replace a part of the string inside the record's field.
For example:
Select * where ItemName LIKE 'O:\location1\%'
The full location example is O:\Location1\filename42a62bcf.dbs
Since the location of the files have been changed I need to preserve the rest of the record but update only "ItemName" field 'O:\location1' with "D:\Location2"
Does anyone have an example please?
0
Comment
Question by:sstouk
3 Comments
 
LVL 2

Accepted Solution

by:
DowntownIT earned 500 total points
ID: 35087089
Here is a basic version. Let me know if you need more


SELECT 'O:\Location1\filename42a62bcf.dbs' AS ItemFile INTO #tmp

SELECT * FROM #tmp

UPDATE #tmp
SET ItemFile = REPLACE(ItemFile,'O:\Location1\','D:\Location2\')
SELECT * FROM #tmp
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 35087105

update table1
set ItemName := REPLACE(ItemName, 'O:\location1', D:\Location2)
where ItemName LIKE 'O:\location1\%'
0
 
LVL 40

Expert Comment

by:Sharath
ID: 35087214
update table1
set ItemName = REPLACE(ItemName, 'O:\location1', 'D:\Location2')
where ItemName LIKE 'O:\location1\%'

Open in new window

0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question