Solved

Replace Function

Posted on 2012-04-12
7
193 Views
Last Modified: 2012-04-12
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
Comment
Question by:nmarano
  • 4
  • 2
7 Comments
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 37839012
UPDATE prospectsNickTemp
        SET homeownerNames = REPLACE(homeownerNames, '<br>', ' ')
--  why do you need this?        WHERE id = #id#
0
 
LVL 2

Author Comment

by:nmarano
ID: 37839027
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
 
LVL 14

Expert Comment

by:nishant joshi
ID: 37839089
nothing is wrong..
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 2

Author Comment

by:nmarano
ID: 37839205
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
 
LVL 39

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 37839283
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
 
LVL 2

Author Comment

by:nmarano
ID: 37839290
got you!  Thanks ged!
0
 
LVL 2

Author Closing Comment

by:nmarano
ID: 37839402
Thanks for helping me get rid of the spaces!
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now