Link to home
Start Free TrialLog in
Avatar of TECH_NET
TECH_NET

asked on

Search and replace a text a SQL column

I have a SQL column that contains a string
something like this

 <script type="text/javascript" src="http://www.notifyjobs.com?ID=somenumber"></script>

I want to change it to

 <script type="text/javascript" src="http://www.notifyjobs.com?JobRequestNum=somenumber"></script>

As you see i just need to change the parameter being passed.( ie replace ID with JobRequestNumber).

How can i achieve this either using trigger on the sql server or through asp.net where i can ready each of the column content and replace the text accordingly before publishing it.

Please note the column data type is nvarchar(max)

Thank you

Avatar of chapmandew
chapmandew
Flag of United States of America image

select replace(' <script type="text/javascript" src="http://www.notifyjobs.com?ID=somenumber"></script>', 'ID', 'JobRequestNum')
Avatar of TECH_NET
TECH_NET

ASKER

UPDATE NOTIFYJOBS_JOB_REGISTRATION
SET DISPLAY_CONTENT_HTML = Replace(Cast(DISPLAY_CONTENT_HTML AS NVARCHAR(Max)),
    'http://www.notifyjobs.com?ID=somenumber',
 'http://www.notifyjobs.com?JobRequestNum=somenumber')

I was able to do it by using the above functionality.

Now can i create a trigger to be fired after the record is inserted. Can anyone provide me the code to do so. I am using sql server 2005.

create trigger tr_table on table for insert
as
begin
UPDATE NOTIFYJOBS_JOB_REGISTRATION
SET DISPLAY_CONTENT_HTML = Replace(Cast(DISPLAY_CONTENT_HTML AS NVARCHAR(Max)),
    'http://www.notifyjobs.com?ID=somenumber',
 'http://www.notifyjobs.com?JobRequestNum=somenumber')
where primarykeyfield in(select primarykeyfield from inserted)
end
replace this

create trigger tr_table on table for insert

with this

create trigger tr_table on NOTIFYJOBS_JOB_REGISTRATION
for insert
Is it same for Table Updates also
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial