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
Last Comment
Kyle Abrahams
Give us a before and after data mockup of what you're trying to pull off here...
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<
AFTER
<text>Z:\ABC\PATH1<text>
BEFORE
<text>H:PATH1\PATH2\PATH3<
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
ASKER
I get the following error:
Argument data type text is invalid for argument 1 of replace function.
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.
ASKER
I understand. I put the column name in and received the above error.
What is the field type for the column (EG: Varchar, xml?)
ASKER
It is text. Do I need to convert the field?
ASKER CERTIFIED SOLUTION
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.