Solved

Change variables in SQL table

Posted on 2016-09-14
6
99 Views
Last Modified: 2016-09-15
Hi,

How do I change all highlighted to D: drive in SQL with one query.

view.jpg
0
Comment
Question by:Member_2_7967487
[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
6 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 41798913
Use the T-SQL Replace keyword to change subsets of string values.  Will probably want to do this a couple of times to handle all the upper case / lower case scenarios.

UPDATE dbo.Company
SET 
   ACCTdirect = REPLACE (ACCTdirect, 'C:\DataMasons' , 'D:\'), 
   EDIdirect = REPLACE (EDIdirect , 'C:\DataMasons' , 'D:\'), 
   vpEDIdirect = REPLACE (vpEDIdirect, 'C:\DataMasons' , 'D:\'), 
   vpSharedirect = REPLACE (ACCTdirect, 'C:\DataMasons' , 'D:\')

Open in new window

0
 
LVL 42

Expert Comment

by:pcelba
ID: 41798941
You don't need to execute the command several times:
UPDATE dbo.Company
SET 
   ACCTdirect = REPLACE (ACCTdirect, 'C:\DataMasons' COLLATE Latin1_General_CI_AS , 'D:'), 
   EDIdirect = REPLACE (EDIdirect , 'C:\DataMasons' COLLATE Latin1_General_CI_AS , 'D:'), 
   vpEDIdirect = REPLACE (vpEDIdirect, 'C:\DataMasons' COLLATE Latin1_General_CI_AS , 'D:'), 
   vpSharedirect = REPLACE (ACCTdirect, 'C:\DataMasons' COLLATE Latin1_General_CI_AS , 'D:')

Open in new window

Of course, if your columns are case insensitive already then the COLLATE is not necessary.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41799068
Try below

UPDATE dbo.Company
SET
   ACCTdirect = REPLACE (ACCTdirect, 'C:\DataMasons' COLLATE Latin1_General_CI_AS , 'D:'),
   EDIdirect = REPLACE (EDIdirect , 'C:\DataMasons' COLLATE Latin1_General_CI_AS , 'D:'),
   vpEDIdirect = REPLACE (vpEDIdirect, 'C:\DataMasons' COLLATE Latin1_General_CI_AS , 'D:'),
   vpSharedirect = REPLACE (ACCTdirect, 'C:\DataMasons' COLLATE Latin1_General_CI_AS , 'D:')
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

Author Comment

by:Member_2_7967487
ID: 41799899
Jim's feedback works best. Thank you, Jim!!

Thanks you everyone for your help!
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41799940
Thanks for the accept.  Good luck with your project.  -Jim

@pcelba and pawan - I'm glad you guys posted, as I didn't know you can combine REPLACE with COLLATE.  I can predict sitting for a SQL 2016 T-SQL exam and getting that as a question, and not answering it correctly if it were not for reading your posts.
0
 
LVL 42

Expert Comment

by:pcelba
ID: 41800070
BTW, I wouldn't say Jim's solution works best it was just faster but it also produces double backslashes on output...  :-)

np for me
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

730 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