Stored procedure to trim and replace

I need a very simple stored procedure to be executed manually from Query Analyzer which will go through every record in a table and replace the first character of the value of a given field with a new one:

for example, I want this sp to go through every record in TABLE1 and replace the "E" from "E:\*--some path--*" with "F:\*--some path--" in a field called PATH1.

I need this solution ASAP and willing to award the maximum points.
shirvaniansAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NightmanCTOCommented:
try this:

UPDATE TABLE1
SET PATH1='F'+SUBSTRING(PATH1,2,LEN(PATH1)-1)
WHERE ISNULL(PATH1,'')<>''
0
dreadyCommented:
The above solution might be a bit dangerous, it will just replace every first character in the path1 column with an F.

Better is this:
Update table1 set path1 = REPLACE(path1,'E:\','F:\')
0
WizillingCommented:
DECLARE @sql NVARCHAR(1000)
SET @sql = 'UPDATE ?
        SET PATH1=''F''+SUBSTRING(PATH1,2,LEN(PATH1)-1)
      WHERE PATH1 like ''E%''
'

EXEC sp_MSForeachtable @sql
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

WizillingCommented:
ignore my statemebnt. i thought u wanted to go through every table.
misunderstood the question. my bad.
0
shirvaniansAuthor Commented:
Hi Wizilling,
I think your suggestion will work. Am I supposed to replace the ? after UPDATE with the table name?

LIKE THIS:

DECLARE @sql NVARCHAR(1000)
SET @sql = 'UPDATE TABLE1
        SET PATH1=''F''+SUBSTRING(PATH1,2,LEN(PATH1)-1)
      WHERE PATH1 like ''E%''
'

EXEC sp_MSForeachtable @sql
0
NightmanCTOCommented:
UPDATE TABLE1
SET PATH1='F'+SUBSTRING(PATH1,2,LEN(PATH1)-1)
WHERE LEFT(PATH1,1)='E'
0
shirvaniansAuthor Commented:
Fantastic, it worked. Let's split the points between the two of you geniuses...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.