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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
WizillingConnect With a Mentor Commented:
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
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
NightmanConnect With a Mentor CTOCommented:
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
All Courses

From novice to tech pro — start learning today.