Newbie needs a little pointing

Question

Post reply

Newbie needs a little pointing

gedkins

Valued Member

Points: 71
More actions
February 1, 2014 at 6:31 am

#303675

I have a MS SQL table that has some entires that hold file paths. I need to change the root level of the paths stored i.e. C:\this_is_the_root\libraries\items needs to have the value changed so the this_is_the_root is changed to something else but retains the latter part of the path. The table has a VariableID column in which the value 5 in that column has in the Value column the actual path that needs updating. What might a SQL call look like to to look at the path value and then replace the old root path with a new one. In some cases the root is the only path defined (no sub paths) and the root path in all cases is exactly the same. Is this a case for using a stored procedure?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

LinksUp SSCertifiable Points: 6526 More actions · Answer 1

I would look at PatIndex http://msdn.microsoft.com/en-us/library/ms188395%28v=sql.100%29.aspx

and SubString http://msdn.microsoft.com/en-us/library/ms187748%28v=sql.105%29.aspx

and CharIndex http://msdn.microsoft.com/en-us/library/ms186323%28v=sql.105%29.aspx

That should get you started with some basic string parsing functions.

For any future questions, you may also want to read the link in the signature line on the correct method of posting questions. It will help in getting actual sql code as answers to your questions!

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

gedkins Valued Member Points: 71 More actions · Answer 2

Thanks very much - truly appreciate the quick response and guidance!

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 3

Gosh. Sound like you don't actually need anything other than a simple REPLACE for this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

gedkins Valued Member Points: 71 More actions · Answer 4

Yup - that did the trick! Too easy.

USE EPDM

Update dbo.SearchFavoriteValues Set Value = replace(Value, 'C:\EPDMDocManager', 'C:\EPDM');

Thanks!

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 5

Thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LinksUp SSCertifiable Points: 6526 More actions · Answer 6

Jeff Moden (2/1/2014)
Gosh. Sound like you don't actually need anything other than a simple REPLACE for this.

Of course!

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/