Avatar of Gary Harper
Gary Harper
Flag for United States of America asked on

Use REPLACE function with like operator

I would like to use the REPLACE function to replace specific text in a field.  I only need to replace that portion of identified text in the field and leave the remaining text before and after the replace intact.  How do I go about achieving this?  I assume the LIKE operator is needed in some fashion?
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
Kyle Abrahams

8/22/2022 - Mon
Jim Horn

Give us a before and after data mockup of what you're trying to pull off here...
Gary Harper

ASKER
In the example below would like to change the path information where field LIKE %H:PATH1\PATH2\PATH3% to %Z:\ABC\PATH1%.  There is text before and after this value.

BEFORE

<text>H:PATH1\PATH2\PATH3<text>

AFTER

<text>Z:\ABC\PATH1<text>
Kyle Abrahams

you can use a charindex instead:

--update table set <field> = replace(<field>, 'h:PATH1\PATH2\PATH3', 'z:\abc\path1') 
select <field> as old, replace(<field>, 'h:PATH1\PATH2\PATH3', 'z:\abc\path1') as new
from table
where charindex( 'h:PATH1\PATH2\PATH3',<field>) > 0

Open in new window

Your help has saved me hundreds of hours of internet surfing.
fblack61
Gary Harper

ASKER
I get the following error:

Argument data type text is invalid for argument 1 of replace function.
Kyle Abrahams

The field is the column name of your table . . . as we don't know your schema the answer needed to be generic.
Gary Harper

ASKER
I understand.  I put the column name in and received the above error.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Kyle Abrahams

What is the field type for the column (EG: Varchar, xml?)
Gary Harper

ASKER
It is text.  Do I need to convert the field?
ASKER CERTIFIED SOLUTION
Kyle Abrahams

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.