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
6 14225
[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]
1. What version of SQL Server do you have?
2. Does your string always confirm to XML rules (i.e. XHTML)?
[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]
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.
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 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?
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Craig Keightley |
last post by:
Can these lines of sql statements be consolidated into one sql statement
(possibly using reg exps??)
BEGIN CODE
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Update...
|
by: Barnes |
last post by:
Does anyone know of a good way to use the JavaScript string.replace() method in an ASP form?
Here is the scenario:
I have a form that cannot accept apostrophes. I want to use the replace() so...
|
by: Danny |
last post by:
I need an asp command to strip out from a string all extra punctuation such
as apostrophe, comma, period, spaces dashes, etc etc and just leave the
letters.
Can anybody give me some ideas?
...
|
by: Peter Row |
last post by:
Hi,
I know this has been asked before, but reading the threads it is still not
entirely clear.
Deciding which .Replace( ) to use when.
Typically if I create a string in a loop I always use a...
|
by: Cor |
last post by:
Hi Newsgroup,
I have given an answer in this newsgroup about a "Replace".
There came an answer on that I did not understand, so I have done some
tests.
I got the idea that someone said,...
|
by: Goran Djuranovic |
last post by:
Hi all,
I ran into a problem where my XMLTextReader fails on .Read() when I have "<" character in one of the attribute's values. What I am trying to do is replace illegal characters ("<", "&" ,...
|
by: TOXiC |
last post by:
Hi everyone,
First I say that I serched and tryed everything but I cannot figure
out how I can do it.
I want to open a a file (not necessary a txt) and find and replace a
string.
I can do it...
|
by: JackpipE |
last post by:
Here is my replace query and I need to run this on every column in my
table. Right now I manually enter the column name (_LANGUAGES_SPOKEN)
but this is time consuming and would like to automate...
|
by: V S Rawat |
last post by:
I was trying to use back-to-back replace functions to convert a url:
str1 =
str.replace("%2F","/").replace("%3F","?").replace("%3D","=").replace("%2
6","&");
It didn't replace all 4 types of...
|
by: =?Utf-8?B?TWlrZSAiWU9fQkVFIiBC?= |
last post by:
I have a text file that contains about 8 to 10 text sequences that I need to
replace.
I want to search and replace all 8 to 10 text sequence anytime I run this
script
Here is what I have so...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |