SQL Replace in Replace 
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
|
January 23rd, 2007, 04:35 PM
|  | 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]
|
January 23rd, 2007, 05:19 PM
|  | 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)?
|
January 23rd, 2007, 05:35 PM
| | 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]
|
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.
|
January 24th, 2007, 08:58 AM
| | 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: - declare @sHTML nvarchar(100)
-
set @sHTML = '<html><body>Hello World, <font size="3">I love my job</font></body></html>'
-
declare @i int
-
-
set @i = patindex('%<%>%', @sHTML)
-
while @i > 0
-
begin
-
set @sHTML = stuff(@sHTML, @i, charindex('>', @sHTML, @i) - @i + 1, '')
-
set @i = patindex('%<%>%', @sHTML)
-
end
-
print @sHTML
-
|
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: - declare @sHTML nvarchar(100)
-
set @sHTML = '<html><body>Hello World, <font size="3">I love my job</font></body></html>'
-
declare @i int
-
-
set @i = patindex('%<%>%', @sHTML)
-
while @i > 0
-
begin
-
set @sHTML = stuff(@sHTML, @i, charindex('>', @sHTML, @i) - @i + 1, '')
-
set @i = patindex('%<%>%', @sHTML)
-
end
-
print @sHTML
-
| 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?
|  |
| | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 225,662 network members.
|
