Solved

SQL Query replace part of string

Posted on 2011-03-09
3
882 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now