Solved

SQL Query replace part of string

Posted on 2011-03-09
3
888 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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: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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Stored procedure 4 38
Get row count of current SQL query 8 64
How to simplify my SQL statement? 14 55
Email Notifications for SQL 2005 9 37
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …

749 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