Q:

Replace a string

i need to replace a string %%firstName%% by [contact.FirstName] and %%lastName%% by [contact.LastName], however these strings can be found on any position in the field
example 

Welcome Aboard %%specialfield2|fallback=||||||%% %%firstname%% %%lastname%%

i need this to become

Welcome Aboard %%specialfield2|fallback=||||||%% [contact.FirstName] [contact.LastName]

note that the first %%specialfield2|fallback=||||||%% remain unchanged
using below query will look for first occurence of %%first..%% and drop the rest
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT SUBJECT,
>   REPLACE((CASE WHEN SUBSTRING(SUBJECT, CHARINDEX('%%', SUBJECT) + 1,
> LEN(SUBJECT) - CHARINDEX('%%', SUBJECT) - CHARINDEX('%%',
> REVERSE(SUBJECT)) ) LIKE '%Firstname%' OR SUBSTRING(SUBJECT,
> CHARINDEX('%%', SUBJECT) + 1, LEN(SUBJECT) - CHARINDEX('%%', SUBJECT)
> - CHARINDEX('%%', REVERSE(SUBJECT)) ) LIKE '%First name%'     THEN REPLACE (SUBJECT, SUBSTRING(SUBJECT, CHARINDEX('%%', SUBJECT) + 1,
> LEN(SUBJECT) - CHARINDEX('%%', SUBJECT) - CHARINDEX('%%',
> REVERSE(SUBJECT)) ),'[Contact.First Name]') ELSE SUBJECT END),'%','')
> 
> FROM CAMPAIGN

CREATE TABLE #tab ( SUBJECT VARCHAR (100))
   INSERT INTO #tab
   SELECT 'Welcome Aboard %%specialfield2|fallback=||||||%% %%firstname%% %%lastname%%'
   SELECT * FROM #tab
   DROP TABLE #tab

by running this query, i can isolate the string between the %% %%
SELECT  SUBJECT
        ,   SUBSTRING(
                        SUBJECT, 
                        CHARINDEX('%%', SUBJECT) + 1, 
                        LEN(SUBJECT) - CHARINDEX('%%', SUBJECT) - CHARINDEX('%%', REVERSE(SUBJECT))

                    )

FROM #tab

image of Result in SQL table

A:

The only place you're going to have problems is if you're using a WHERE clause with %. REPLACE just replaces the found substring with another substring.
Let's start with this. I've put %%firstname%% and %%lastname%% in different places.
CREATE TABLE #tab
(
    SUBJECT VARCHAR(100)
)

INSERT #tab select 'Welcome Aboard %%specialfield2|fallback=||||||%% %%firstname%% %%lastname%%'
INSERT #tab select 'Welcome Aboard %%specialfield2|fallback=||||||%%%%lastname%% %%firstname%% test here'
INSERT #tab select 'Welcome Aboard %%firstname%%%%lastname%% %%specialfield2|fallback=||||||%%'
INSERT #tab select 'Welcome Aboard %%firstname%% %%specialfield2|fallback=||||||%% %%lastname%%'
INSERT #tab select 'Welcome Aboard'

You could just do this:
UPDATE #tab set [SUBJECT]=REPLACE(REPLACE([SUBJECT], '%%firstname%%', '[contact.FirstName]'), '%%lastname%%', '[contact.LastName]')

If you want a WHERE clause, you'll have to escape the %: How do I escape a percentage sign in T-SQL?
UPDATE #tab set [SUBJECT]=REPLACE(REPLACE([SUBJECT], '%%firstname%%', '[contact.FirstName]'), '%%lastname%%', '[contact.LastName]')
WHERE [SUBJECT] LIKE '%[%][%]%name[%][%]%'

