Replace Function

Experts-

I am trying to replace any "<br>" that is returned from my query.  Can someone help me with the replace function because it doesn't seem that I have it correct.....

     select *
    from prospectsNickTemp
    where homeownerNames Like '%<br>%'
    or agentNotes Like '%<br>%'
    or notes Like '%<br>%'

The update that I am trying to use but breaks is....

UPDATE prospectsNickTemp
        SET homeownerNames = REPLACE(homeownerNames, '<br>', ' ')
        WHERE id = #id#

Can someone tell me what I am doing wrong.

Thanks
Nick
LVL 2
nmaranoAsked:
Who is Participating?
 
Kyle AbrahamsConnect With a Mentor Senior .Net DeveloperCommented:
UPDATE prospectsNickTemp
        SET homeownerNames = REPLACE(homeownerNames, '<br>', '')


You either have a space before or after the <br> which what you're seeing.

You may want to run 3 update statements:


--remove space after
UPDATE prospectsNickTemp
        SET homeownerNames = REPLACE(homeownerNames, '<br> ', '')

--remove space before
UPDATE prospectsNickTemp
        SET homeownerNames = REPLACE(homeownerNames, ' <br>', '')

--remove no space.
UPDATE prospectsNickTemp
        SET homeownerNames = REPLACE(homeownerNames, '<br>', '')
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
UPDATE prospectsNickTemp
        SET homeownerNames = REPLACE(homeownerNames, '<br>', ' ')
--  why do you need this?        WHERE id = #id#
0
 
nmaranoAuthor Commented:
where id = #id# is the query running within coldfusion, but if I can just do it in enterprise manager query that would work also
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
nishant joshiTechnology Development ConsultantCommented:
nothing is wrong..
0
 
nmaranoAuthor Commented:
SO I do have it working, but one question.  How can it is currently leaving a space where the replace is happening,
UPDATE prospectsNickTemp
        SET homeownerNames = REPLACE(homeownerNames, '<br>', '')

I removed the space in my single quotes, but it shows a space in the table.  Any suggestions?

Thanks
Nick
0
 
nmaranoAuthor Commented:
got you!  Thanks ged!
0
 
nmaranoAuthor Commented:
Thanks for helping me get rid of the spaces!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.