Connecting Tech Pros Worldwide Help | Site Map

SQL Replace in Replace

  #1  
Old January 23rd, 2007, 01:06 PM
Newbie
 
Join Date: Jan 2007
Posts: 2
Hi

I'm looking for a way to get a substring using replace. I've heard that this may be possible by using a kind of nested replace.
My problem is that I have a text like

<font size=3>I love my job</font>

and I want to end up with,

I love my job

Thank you
  #2  
Old January 23rd, 2007, 04:35 PM
iburyak's Avatar
Expert
 
Join Date: Nov 2006
Posts: 1,017

re: SQL Replace in Replace


[PHP]declare @sHTML varchar(50)
set @sHTML = '<font size=3>I love my job</font>'
select substring(@sHTML,charindex('<font size=3>',@sHTML) + len('<font size=3>'),charindex('</font>',@sHTML) - len('<font size=3>') -1)[/PHP]
  #3  
Old January 23rd, 2007, 05:19 PM
almaz's Avatar
Expert
 
Join Date: Dec 2006
Location: Kyiv, Ukraine
Posts: 167

re: SQL Replace in Replace


1. What version of SQL Server do you have?
2. Does your string always confirm to XML rules (i.e. XHTML)?
  #4  
Old January 23rd, 2007, 05:35 PM
iburyak's Avatar
Expert
 
Join Date: Nov 2006
Posts: 1,017

re: SQL Replace in Replace


[PHP]Declare @sHTML varchar(50), @Tag1 varchar(50), @Tag2 varchar(50)
Set @Tag1 = '<font size=3>'
Set @Tag2 = '</font>'

select Replace(Replace( '<font size=3>I love my job</font>',@Tag1,''),@Tag2, '')[/PHP]
  #5  
Old January 24th, 2007, 07:48 AM
Newbie
 
Join Date: Jan 2007
Posts: 2

re: SQL Replace in Replace


Quote:
Originally Posted by almaz
1. What version of SQL Server do you have?
2. Does your string always confirm to XML rules (i.e. XHTML)?

Hi

I use a SQL Server 2005.
  #6  
Old January 24th, 2007, 08:58 AM
almaz's Avatar
Expert
 
Join Date: Dec 2006
Location: Kyiv, Ukraine
Posts: 167

re: SQL Replace in Replace


I assume that your task is to remove all tags from the string.
SQL Server is not well-suited for string manipulations, so in SQL Server 2005 I would recommend using CLR function that will do the trick. If you don't want to invoke .NET for this task, this sample may solve your problem:
Expand|Select|Wrap|Line Numbers
  1. declare @sHTML nvarchar(100)
  2. set @sHTML = '<html><body>Hello World, <font size="3">I love my job</font></body></html>'
  3. declare @i int
  4.  
  5. set @i = patindex('%<%>%', @sHTML)
  6. while @i > 0
  7. begin
  8.     set @sHTML = stuff(@sHTML, @i, charindex('>', @sHTML, @i) - @i + 1, '')
  9.     set @i = patindex('%<%>%', @sHTML)
  10. end
  11. print @sHTML
  12.  
  #7  
Old February 21st, 2007, 02:56 PM
Newbie
 
Join Date: Feb 2007
Posts: 1

re: SQL Replace in Replace


Quote:
Originally Posted by almaz
I assume that your task is to remove all tags from the string.
SQL Server is not well-suited for string manipulations, so in SQL Server 2005 I would recommend using CLR function that will do the trick. If you don't want to invoke .NET for this task, this sample may solve your problem:
Expand|Select|Wrap|Line Numbers
  1. declare @sHTML nvarchar(100)
  2. set @sHTML = '<html><body>Hello World, <font size="3">I love my job</font></body></html>'
  3. declare @i int
  4.  
  5. set @i = patindex('%<%>%', @sHTML)
  6. while @i > 0
  7. begin
  8.     set @sHTML = stuff(@sHTML, @i, charindex('>', @sHTML, @i) - @i + 1, '')
  9.     set @i = patindex('%<%>%', @sHTML)
  10. end
  11. print @sHTML
  12.  
I have another problem, for example if i have situation like:

set @sHTML = '<P><FONT style=%22BACKGROUND-COLOR: #ffff99%22><STRONG><FONT color=#009900></FONT></STRONG></FONT></P><P>bbbb</P>'

this query fails because of % in html tag and it doesn't replace it well...
how can i solve this?
Reply


Similar Threads
Thread Thread Starter Forum Replies Last Post
Replace in a Select query doesn't work Robert answers 8 November 20th, 2005 08:56 AM
Help using REPLACE in a query skinnybloke answers 8 November 12th, 2005 11:08 PM
Help using REPLACE in a query skinnybloke answers 8 November 12th, 2005 10:43 PM
ASP, Excel and SQL Replace function adrian zaharia answers 5 July 22nd, 2005 01:12 AM