[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

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
0
nmarano
Asked:
nmarano
  • 4
  • 2
1 Solution
 
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
 
nishant joshiTechnology Development ConsultantCommented:
nothing is wrong..
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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
 
Kyle AbrahamsSenior .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
 
nmaranoAuthor Commented:
got you!  Thanks ged!
0
 
nmaranoAuthor Commented:
Thanks for helping me get rid of the spaces!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now