Professional Web Applications Themes

using a sql statement to do a string replace - Microsoft SQL / MS SQL Server

I am not certain which group to post this to - sorry if this is the wrong group: I have a table that holds the location of various media and such (mostly images). One field is the Media name, the other is the URL in the form http://myServer.gunnery.org/media/pic1 for example. we are about to move all the media to a new location (new server). the paths will remain the same except for the server name. Is there an update statement that will let me go through the table an replace myServer with newServer in all the URLs (and leave the ...

  1. #1

    Default using a sql statement to do a string replace

    I am not certain which group to post this to - sorry if this is the wrong
    group:

    I have a table that holds the location of various media and such (mostly
    images). One field is the Media name, the other is the URL in the form
    http://myServer.gunnery.org/media/pic1 for example. we are about to move
    all the media to a new location (new server). the paths will remain the
    same except for the server name.

    Is there an update statement that will let me go through the table an
    replace myServer with newServer in all the URLs (and leave the rest of the
    URL alone). So the link above would become
    http://newServer.gunnery.org/media/pic1. I know how I would do it with VB,
    but I am not as familar with all the tricks in SQL.

    thanks

    bj daniels
    org


    bj Guest

  2. #2

    Default Re: using a sql statement to do a string replace

    You should be able to use the REPLACE function. This select statement should show you that it works as expected:

    SELECT REPLACE(MediaName, 'myServer.gunnery', 'newserver.gunnery') FROM YourTable


    After verifying that the select gives you the expected result, run the following update to update the data within the table:

    --UPDATE YourTable SET MediaName = REPLACE(MediaName, 'myServer.gunnery', 'newserver.gunnery')
    --SELECT rowcount AS RowsUpdated

    --
    Keith

    "bj daniels" <org> wrote in message news:phx.gbl... 
    Keith Guest

  3. #3

    Default Re: using a sql statement to do a string replace

    Looks Perfect - thanks!


    "Aaron Bertrand - MVP" <com> wrote in message
    news:phx.gbl... 
    '//newServer') [/ref]
    wrong [/ref]
    move [/ref]
    the 
    > VB, 
    >
    >[/ref]


    bj Guest

Similar Threads

  1. syntax error in replace statement
    By middletree in forum ASP
    Replies: 8
    Last Post: October 24th, 05:30 PM
  2. Replies: 4
    Last Post: September 12th, 04:19 PM
  3. using a sql statement to do a string replace
    By bj in forum ASP.NET General
    Replies: 2
    Last Post: August 20th, 07:19 PM
  4. SQL statement to replace spaces between words with underscore
    By Don in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 13th, 09:30 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139