Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sjfraser00
Starting Member
3 Posts |
 Posted - 2011-03-15 : 12:35:06
|
| This might take some explaining but here goes, we have a ntext column in our cms database that has links to downloadable documents etc etc buried in its CDATA.I have been busy filtering and extracting the filenames and titles from this column by a routine in VS and moving the files themselves and the data into their own table/folder for use in a stand alone File manager plugin.now to the problem.I now have several hundred files and their associated data ready to publish to the plugin but I first need to able to replace the original links still in the page data with something like "title of file" + "see file manager below" In VS I can successfully strip the complete <a href>--</a> from the page data, build a data table and send each url as a parameter to an update Replace statement which finds the string and replaces with some new text.The problem is the search fails whenever it encounters \r or \n nearly always found in the title portion of the URL. we have tried all manner of replacing and stripping out characters both in VS and SQL without resolving it.So is there a way to send the URL bit which works in the search and when we update also append a specified number of characters to the right which we can pass as a parameter from VS.long winded I know but would appreciate some ideas.SJF |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2011-03-16 : 04:23:33
|
| Can you post some sample data with expected result?MadhivananFailing to plan is Planning to fail |
 |
|
|
sjfraser00
Starting Member
3 Posts |
Posted - 2011-03-16 : 04:51:45
|
| As this might be difficult to understand I add a bit more on what I am trying to do, I dont know if it is even possible in SQL but we are becoming desperate to avoid doing it manually.In visual studio we strip out all the links and pass them as parameters along with a page id to a SP which searches until it finds the parameter string and replaces it with a new string (similar to the snippet below)UPDATE TableSET [p_content] = cast(REPLACE(cast([p_content] AS varchar(8000)),@Searchtxt,@Newtxt) AS ntext)WHERE p_content LIKE '%' + @Searchtxt +'%' and ID = @IDHowever the page data has carrige returns and new line characters in the title part of the URL and the search fails at this point.<a href="pdf/blah.pdf">Corporate Complaints\r\n Policy</a>We have tried all manner of replacing or converting to spaces etc etc but it never makes any difference.So we thought we could send the URL bit as the search parameter <a href="pdf/blah.pdf"> which does work then count the number of characters to the right and send this as a seperate parameter.If it is possible we could then do the replace on the search string <a href="pdf/blah.pdf"> + 62 characters to the right.I am sure at somepoint in the past I have done something similar where we had a starting and end point in the query, but I cannot find an example.can any one help or offer any other solution.SJF |
|
|
|
sjfraser00
Starting Member
3 Posts |
Posted - 2011-03-16 : 05:05:19
|
| Very curious.If we right click on one the p_content columns then copy and paste the data into a ntext column in a new table, then run the update replace query it works perfectly.So there must be some sort of encoding going on here?SJF |
|
|
|
|
|
|
Select portion ostring plus a number of characters