replace and % - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

replace and %

Author

Topic

tpk
Starting Member

10 Posts

Posted - 2003-03-26 : 08:46:49

In EXCEL I can do the following replace function to strip out all tags and the contents between them.
FIND: <*>*</*>
REPLACE WITH:
Replacing with nothing to remove it.

It would be nice and quick if I could do this in sql:
REPLACE(@myString,'<%>%</%>', '')

But no luck. Can anyone tell me how to do this?

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-03-26 : 09:04:52

Hi

Not easily.
However, if you do a forum search on "Regular Expressions" you will find a link to a 3rd party extended stored proc that gives Regex support.

With that, and some clever regular expressions you should be able to do something.

Or, you could write something in VB or some other language with Regex support that will do the row update for you.

Otherwise, if it is just a one off operation, you might be best off dumping out your data, changing it, then dumping it back in.

Damian

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-03-26 : 09:25:18

Are you trying something like this ??

 
Use tempdb
GO
ALTER Function RepHtmlCodes (@str varchar(1000))
Returns varchar(1000)
as
Begin
	Declare @i int, @j int
	select @i=charindex('<',@str,1)
	while @i>0
	begin
		select @j=charindex('>',@str,@i+1)
		set @str=stuff(@str,@i,@j-@i+1,'')
		select @i=charindex('<',@str,1)
	end
	Return @str
End
GO
select testerwin.dbo.RepHtmlCodes('<one>###</one>ABCDEF<two>1234</two>') as 'Final'

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.

tpk
Starting Member

10 Posts

Posted - 2003-03-26 : 09:34:01

Thats almost exactly it samsekar!

Except that your function is not removing the text inbetween the tags. It puts me on the right track though, thanks.

tpk
Starting Member

10 Posts

Posted - 2003-03-26 : 13:23:54

Thanks to samsekar's example I think I've written a function to do what I want now. It seems to work provided the input has well formed html tags.


CREATE FUNCTION RemTags (@str varchar(4000))
Returns varchar(4000)
AS
BEGIN

	DECLARE @StartTagPos int, @CloseTagStartPos int, @CloseTagEndPos int
	SELECT @StartTagPos=CHARINDEX('<',@str,1)
	WHILE @StartTagPos>0
	BEGIN
		SELECT @CloseTagStartPos = CHARINDEX('</',@str)
		SELECT @CloseTagEndPos = CHARINDEX('>',@str,@CloseTagStartPos)
		SET @str=STUFF(@str,@StartTagPos,@CloseTagEndPos-@StartTagPos+1,'')
		SELECT @StartTagPos=CHARINDEX('<',@str,1)
	END
	RETURN @str	

END

SELECT dbo.RemTags('abcdef<tag>g</tag>hijklm')

Edited by - tpk on 03/26/2003 13:25:48

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-26 : 14:43:17

Good solution. If you're interested, the PATINDEX function (see BOL) uses SQL regular expressions. The only advantage I can see is PATINDEX will find the entire match or return zero. That is < would require > to be present to generate a match.

In your example code below, an error would be generated if the right hand delimiter was missing.

Sam

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-03-26 : 17:45:33

I stand corrected

Damian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources