SQL Replace in Replace

Martin Grove

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

I love my job

and I want to end up with,

I love my job

Thank you

iburyak

[PHP]declare @sHTML varchar(50)
set @sHTML = 'I love my job'
select substring(@sHTML,charindex('',@sHTML) + len(''),charindex('',@sHTML) - len('') -1)[/PHP]

almaz

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

iburyak

[PHP]Declare @sHTML varchar(50), @Tag1 varchar(50), @Tag2 varchar(50)
Set @Tag1 = ''
Set @Tag2 = ''

select Replace(Replace( 'I love my job',@Tag1,''),@Tag2, '')[/PHP]

Martin Grove

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.

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

 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

skoco

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

 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 = 'bbbb'

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

SQL Replace in Replace

re: SQL Replace in Replace

re: SQL Replace in Replace

re: SQL Replace in Replace

re: SQL Replace in Replace

re: SQL Replace in Replace

re: SQL Replace in Replace

Similar Microsoft SQL Server bytes

/bytes/development

/bytes/it

/bytes/about