?
Solved

SQL Query replace part of string

Posted on 2011-03-09
3
Medium Priority
?
892 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 2000 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:Ephraim Wangoya
ID: 35087105

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

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

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

862 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