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 |
|
vpekulas
Starting Member
5 Posts |
 Posted - 2012-08-17 : 04:18:29
|
| Hi Guys,trying to figure out how to replace random text between 2 words.For example: this is the text that is random but two words are setWhat I need is to create a SQL to replace the text between the words text and two with say a word new.Any hints are very welcomed ! :) Thank you. |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-08-17 : 08:24:53
|
| This works on the example you provided.declare @str varchar(100) = 'this is the text that is random but two words are set'SELECT STUFF(@str ,PATINDEX('%text %',@str) +5 ,PATINDEX('% two%',@str)-PATINDEX('% text%',@str)-6 ,'New' )JimEveryday I learn something that somebody else already knew |
 |
|
|
vpekulas
Starting Member
5 Posts |
Posted - 2012-08-20 : 09:49:00
|
| Thank you for the example. Now this works on a given row.Though how do you make it loop throught say a field 'fldA' in a table 'TBL_TABLE' in every row available in the table?Thanks ! |
|
|
|
Abu-Dina
Posting Yak Master
206 Posts |
Posted - 2012-08-20 : 10:10:18
|
| [code]SELECT STUFF(YourColumn,PATINDEX('%text %',YourColumn) +5,PATINDEX('% two%',YourColumn)-PATINDEX('% text%',YourColumn)-6,'New' )from YourTable[/code] |
|
|
|
vpekulas
Starting Member
5 Posts |
Posted - 2012-08-20 : 11:28:20
|
| This was too easy to be true :)When I execute this I get:Argument data type text is invalid for argument 1 of stuff function.Done some searching and realized that it doesn like TEXT datatype, so I've casted to varchar(8000) and now it seems to just erase everything rather than just the part in between the 2 strings:UPDATE TBL_ISSUE SET fldA = STUFF(Cast(fldA as varchar(8000)),PATINDEX('display:none',fldA) +5 ,PATINDEX('</SPAN>',fldA)-PATINDEX('display:none',fldA)-6 ,'New text inserted') WHERE ID = 1001Any ideas why? Thank you ! |
|
|
|
Abu-Dina
Posting Yak Master
206 Posts |
Posted - 2012-08-21 : 04:03:16
|
quote:
Originally posted by vpekulas
This was too easy to be true :)When I execute this I get:Argument data type text is invalid for argument 1 of stuff function.Done some searching and realized that it doesn like TEXT datatype, so I've casted to varchar(8000) and now it seems to just erase everything rather than just the part in between the 2 strings:UPDATE TBL_ISSUE SET fldA = STUFF(Cast(fldA as varchar(8000)),PATINDEX('display:none',fldA) +5 ,PATINDEX('</SPAN>',fldA)-PATINDEX('display:none',fldA)-6 ,'New text inserted') WHERE ID = 1001Any ideas why? Thank you !
If you provide some sample data then I'm sure someone will be able to help! |
|
|
|
vpekulas
Starting Member
5 Posts |
Posted - 2012-08-22 : 06:31:15
|
| Good point, here goes sample data:<EM>not </EM>include a free installation.<BR><BR>Questions? Please [ <A title="Click for ways to contact staff." href="/contactus.asp">contact us</A> ].<!-- Antelisthesis ideate commorancy whalebone muse leg sculpt nizfuhue sordino. --></P><span style='display:none'><br />Hosing orienteering, adenomyofibroma!<br><br /><i>Demating</i> gallows <b>nonconducting</b> affairs brewery dibromobenzene cheloid groundsman goffer cartage philosophize coining retractility.<br><br /><a href="http://denver.mixliving.com/article.asp?ArticleID=10722">losartan</a> <a href="http://www.truckinginfo.com/news/news-print.asp?news_id=67709">purchase viagra</a> <a href="http://www.expinion.net/_news/view.asp?ID=112">buspirone</a> <b>pseudoheme</b> <a href="http://www.smukfest.dk/sidstenyt_vis.asp?id=657#38;id1=227#38;id2=275#38;id3=0#38;id4=0">singulair</a> <a href="http://www.shemaroo.com/online/detailsynopsis.asp?id=867419913#38;productid=1017#38;buy-carisoprodol-online">buy carisoprodol online</a> </SPAN><br />some mroe text hereBasically the text between and including the red tags was injected into 100's of rows as part of some text. And I need to get rid of it, or rather only the part with the links that always starts with <span style='display:none'> and end with </span> . The easiest way for me to get rid of this was to replace anything in between the start SPAN and end SPAN tags. The field is TEXT type in MS-SQL. Ideas? |
|
|
|
vpekulas
Starting Member
5 Posts |
Posted - 2012-08-27 : 04:13:17
|
| Anyone please? |
|
|
|
|
|
|
|
|
Replace function - in between