REPLACE TEXT IGNORE ENCLOSED BY '<' '>'

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

REPLACE TEXT IGNORE ENCLOSED BY '<' '>'

Author

Topic

SQLapprentice
Starting Member

29 Posts

Posted - 2012-02-14 : 04:57:12

Hi all,

I've been trying to replace a string in a text column, but i just want to replace the corresponding string if it is outside of these characteres '<'''>' like: <' String '>';

Example:

command= UPDATE versao SET text = REPLACE (text, 'hello', 'Hi') WHERE texto LIKE '%"hello"%'and section='1'and paragraph='2'";

I want to do this in the text column but ignoring the parts of the text like <hello>

Thanks in advance,

Regards

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-14 : 05:05:46

declare @test varchar(255)
set @test = 'This is Sparta! Hello <Hello> Hello'
select
replace(replace(replace(@test,'<Hello>','*#*'),'Hello','Hi'),'*#*','<Hello>')

No, you're never too old to Yak'n'Roll if you're too young to die.

SQLapprentice
Starting Member

29 Posts

Posted - 2012-02-14 : 05:50:57

Thank you for your answer,

I've tried it, but it replaces everything the same way. I think I haven't explained well.

I have in text strings like:

Hello, Hello
<map name=act2 onmouseover=parent.changeColor(this.name,this.id) id=lex1 onmouseout=parent.removeColor(this.name,this.id)> Hello</map> Hello, Hello

entered the command:

mysql> UPDATE versao SET texto = replace(replace(replace(texto,'>Hello<','*#*'),'o
la','Hi'),'*#*','<kk>') WHERE texto LIKE '%Hello%';

But it replaced every occurrences the same way... I want the red Hello not to be replaced..

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-14 : 05:57:09

Have a look at what you are mixing there.

You are replacing '>Hello<' by '*#*'
Then you are replacing 'ola'by 'Hi'
Then you are replacing '*#*' back to <kk>

I think you have not understand the solution...

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-14 : 06:01:16

The given solution means:

1. replace the part that you want NOT to be changed to something that you can identify later, hence >Hello< to *#* for example.
This is the innermost replace.
2. replace what you want to replace
3. replace *#* back to >Hello<
done!

No, you're never too old to Yak'n'Roll if you're too young to die.

SQLapprentice
Starting Member

29 Posts

Posted - 2012-02-14 : 06:03:26

Thanks I paste the wrong command.

But there are some situations like >Helloo< or >oooHelloo< that's why I think is not working

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-14 : 06:06:39

Yes - changing the example after someone has given a solution - that's what I like

You're right, my solution isn't working if the text is varying.

No, you're never too old to Yak'n'Roll if you're too young to die.

SQLapprentice
Starting Member

29 Posts

Posted - 2012-02-14 : 06:15:14

SQLapprentice
Starting Member

29 Posts

Posted - 2012-02-14 : 06:22:45

Is there a way of doing it like ignoring the text between > and ola and between ola and <?

SQLapprentice
Starting Member

29 Posts

Posted - 2012-02-14 : 06:43:13

I think this will do it

UPDATE versao SET texto = replace(replace(replace(replace(replace(texto,'ola>','*##*'),'>ola','*#*'),'ola','Hi'),'*#*','>ola'),'*##*','ola<') WHERE texto LIKE '%ola%';

SQLapprentice
Starting Member

29 Posts

Posted - 2012-02-14 : 06:55:26

working:

mysql> UPDATE versao SET texto = replace(replace(replace(replace(replace(texto,'
ola<','*##*'),'>ola','*#*'),'ola','Hi'),'*#*','>ola'),'*##*','ola<') WHERE texto
LIKE '%ola%';

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources