Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2004
    Posts
    12

    Unanswered: Repalce partial string in table

    Hi! I need to replace a string in a SQL table with a new path.

    Essentially, I want to find and replace the share path while retaining the rest of the path past that point.

    I tried using Update (on a test table of course) but it sompley find and replaces with no regards to the rest of the path.

    Any help is greatly appreciated!

    JJ

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149
    DDL and some sample data would be helpful

  3. #3
    Join Date
    Apr 2004
    Posts
    12

    Sample data

    http://www.apcit.com/work/john.gif

    I need to replace a portion of the path in the source column with a new vaule but still retain the rest of the text.

    Hope this helps!

    JJ

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    DECLARE @Path varchar(255), @find varchar(25), @replace varchar(25)

    SELECT @Path = 'C:\myTemp99\Brett\Folders\',@find = 'Brett', @replace = 'JJ'

    SELECT @Path = REPLACE(@Path,@find,@replace)

    SELECT @Path
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Apr 2004
    Posts
    12
    Brett,

    Thanks for your reply but how do I specify a table within the database?

    For clarity:

    I want to open a table, for a given object in the table I want to replace a portion of the value with a new one and append the rest of the data to the end of the new value.

    www.apcit.com/work/john.gif

    Essentially the path to the SOURCE has changed and I just want to find and repalce.

    Thanks again!

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd try something like:
    PHP Code:
    UPDATE junque
       SET pathname 
    Replace(pathname'/work/''/play/')
       
    WHERE 0 CharIndex(pathname'/work/'
    Do check it out on a scratch table first though, this could cause havok if misused!

    -PatP

  7. #7
    Join Date
    Apr 2004
    Posts
    12

    Still trying....

    I'm actually using SQL query analyzer to run these, so please use that syntax.

    Trying to resolve the @PATH var specified in the earlier post. I'm formatting it something like this:

    @Path = 'sms_xxx.dbo.smspackages.source'

    Is this correct? Basically sms_xxx is the DB. smspackages is the table and source is the column I want to find and replace the value in.

    Any help is golden!

    Thanks!

    JJ

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54

    Re: Still trying....

    Originally posted by jjrenner
    I'm actually using SQL query analyzer to run these, so please use that syntax.

    Trying to resolve the @PATH var specified in the earlier post. I'm formatting it something like this:

    @Path = 'sms_xxx.dbo.smspackages.source'

    Is this correct? Basically sms_xxx is the DB. smspackages is the table and source is the column I want to find and replace the value in.

    Any help is golden!

    Thanks!

    JJ
    These all seem to be acceptable syntax for SQL Query Analyzer.

    The @path in Brett's example was a declared variable that he was using to illustrate the concept.

    I'd suggest that you use something vaguely like:
    PHP Code:
    UPDATE sms_xxx.dbo.smspackages
       SET source 
    Replace(source'original''new'
    -PatP

  9. #9
    Join Date
    Apr 2004
    Posts
    12
    Pat,

    Worked like a charm. Thanks for the baby steps!

    JJ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •