Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Text field manipulation - help

Status
Not open for further replies.
DotNetGnat

DotNetGnat

Programmer
Mar 10, 2005
5,548
IN
Guys,

lets say i have a text field which has text like below
Code: 
<html><body > <b>Continuing  Education/Training</b><br><br>"USAOC  Wetland Delineatior", FAA Course # 1200, National Environmental Policy Act  Rutgers University: Stormwater Management, HEC1, Basic  Plant Id. for Wetland Delineation, Introduction to Wetland Id., and Coastal  Wetland Plant Id.[red]<br>   <br><br>      [/red]</body></html>


question:

how can i remove the tags shown in red ONLY from the end of the text...i mean there can be valid <br> tags in between the document but i only want to remove them from the end...

the below is what i have in mind...

Code: 
Declare @ptr binary(16)

select @ptr=textptr(mytextfld) from mytable

--code to find and remove the <br> tags from the end

use updateText to update the tags with ''

any suggestions...thanks

-DNG
 
Sort by date Sort by votes
started off like this:

Code: 
Create table #temp (blah text)
insert into #temp values('<html><body > <b>Continuing  Education/Training</b><br><br>"USAOC  Wetland Delineatior", FAA Course # 1200, National Environmental Policy Act  Rutgers University: Stormwater Management, HEC1, Basic  Plant Id. for Wetland Delineation, Introduction to Wetland Id., and Coastal  Wetland Plant Id.<br>   <br><br>      </body></html> ')

--Select * from #temp

Declare @ptr as binary(16)
Declare @startpos as int
Declare @endpos as int

Select @ptr=textptr(blah)
       from #temp

SELECT @startpos=PatIndex('%<br>%',blah)
	from #temp
print @startpos
drop table #temp

but how to make the PatIndex return the tags in the end...

thanks

-DNG
 
Upvote 0 Downvote
Hi,

If you are saying that some <br> tags are good (middle of the code) and others are not (end of the code)


Then your only real method is to use some sort of brute force kludge to read from the bottom to the top removing (ignoring) the </html> and <body. then run an Rtrim then test the last bit again.

all and all not really a good idea in T-SQL. Text manipulation is best left to a true programming language on your front end.

That being said I was bored and felt like making some kludge:

Code: 
declare @text varchar(500)
set @text ='<html><body > <b>Continuing  Education/Training</b><br><br>"USAOC  Wetland Delineatior", FAA Course # 1200, National Environmental Policy Act  Rutgers University: Stormwater Management, HEC1, Basic  Plant Id. for Wetland Delineation, Introduction to Wetland Id., and Coastal  Wetland Plant Id.<br>   <br><br>      </body></html> '
 
set @text = left(@text,charindex(upper('</html>'), upper(@text))-1)
set @text = left(@text,charindex(upper('</body>'), upper(@text))-1)
set @text = ltrim(rtrim(@text))

While right(upper(@text),4) = upper('<br>')

Begin
set @text = ltrim	(
				rtrim	( 
					left(@text,len(@text)-4)
						)
					)
End
print @text




Bassguy

 
Upvote 0 Downvote
thanks Bassguy,

but you have declared the variable as varchar...which i dont think i can do it in my case since most of them are longer then 8000 character...

-DNG
 
Upvote 0 Downvote
I'm not an expert in them, but this looks like a job for a small app using regular expressions. Especially if this is data coming from a feed; then it could easily be run as preprocessing before the data got into the table.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief
 
Upvote 0 Downvote
due to the size I would agree then with the sql sista

bassguy
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

f0rg3tfu1
  • Locked
  • Question
PLSQL Package Help
Replies
0
Views
197
f0rg3tfu1
f0rg3tfu1
Andrzejek
  • Locked
  • Question
Field types question
Replies
9
Views
229
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
497
Chris Miller
Chris Miller
grnzbra
  • Locked
  • Question
Adding Text to and XML Field 1
Replies
2
Views
110
grnzbra
grnzbra
Cpreston
  • Locked
  • Question
Adding field duplicates records
Replies
16
Views
206
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top