473,321 Members | 1,669 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

SQL Replace in Replace

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
Jan 23 '07 #1
6 14225
iburyak
1,017 Expert 512MB
[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]
Jan 23 '07 #2
almaz
168 Expert 100+
1. What version of SQL Server do you have?
2. Does your string always confirm to XML rules (i.e. XHTML)?
Jan 23 '07 #3
iburyak
1,017 Expert 512MB
[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]
Jan 23 '07 #4
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.
Jan 24 '07 #5
almaz
168 Expert 100+
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.  
Jan 24 '07 #6
skoco
1
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?
Feb 21 '07 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

4
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...
12
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...
6
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? ...
9
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...
4
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,...
3
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 ("<", "&" ,...
3
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...
6
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...
5
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...
15
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
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...
1
isladogs
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...
0
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...
0
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...
0
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...
0
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
0
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...
0
isladogs
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.