Link to home
Start Free TrialLog in
Avatar of Gary Harper
Gary HarperFlag 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?
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Give us a before and after data mockup of what you're trying to pull off here...
Avatar of 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>
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

I get the following error:

Argument data type text is invalid for argument 1 of replace function.
The field is the column name of your table . . . as we don't know your schema the answer needed to be generic.
I understand.  I put the column name in and received the above error.
What is the field type for the column (EG: Varchar, xml?)
It is text.  Do I need to convert the field?
ASKER CERTIFIED SOLUTION
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
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