zig zig - 9 days ago 5
SQL Question

How to replace a word which has a specific pattern in text?

I have a

ntext
(or
nvarchar(max)
) column which contains HTML text for example:

<P>Hello <IMG title="" border=0 alt="" src="files/1.JPG"></P>
<P>&nbsp;</P>
<P><IMG title="" border=0 alt="" src="files/folder1/2.JPG"></P>
<P>&nbsp;</P> World!
<P><IMG title="" border=0 alt="" src="files/folder2/files/3.JPG"></P>
<P>&nbsp;</P>
<P><IMG title="" border=0 alt="" src="files/4.JPG"></P>


Now I need to replace each path of the
src
from
files/
to
files/new/
(or to other path) but only those with path of
files/
.

a
REPLACE
won't work (I think) because it would change also
files/folder1/
and
files/folder2/
.

In the above example, I need to only change the path for
1.JPG
and
4.JPG
.

How can I do it? (The question is specific to sql-server).

Note: The file names/content can vary. the above HTML is just an example.

Answer

Following a quick approach in order to replace a certain pattern within a nvarchar(max) string. To be more precisely: the code takes a nvarchar(max) variable @x as source and writes the transformed string into a new variable @y. However, I wrote only the part of the string replacement - the "Update" still has to be coded.

DECLARE @x nvarchar(max) = '<P>Hello <IMG title="" border=0 alt="" src="files/1.JPG"></P>
<P>&nbsp;</P>
<P><IMG title="" border=0 alt="" src="files/folder1/2.JPG"></P>
<P>&nbsp;</P> World!
<P><IMG title="" border=0 alt="" src="files/folder2/files/3.JPG"></P>
<P>&nbsp;</P>
<P><IMG title="" border=0 alt="" src="files/4.JPG"></P>'

DECLARE @brPos int = (SELECT CHARINDEX('</P>', @x));
DECLARE @brPosPrev int = 0;

DECLARE @srcPos int;
DECLARE @SlashPos int;
DECLARE @JPGPos int;

DECLARE @y nvarchar(max) = '';

DECLARE @xPart nvarchar(max);

WHILE (@brPos != 0)
BEGIN
  SET @xPart = SUBSTRING(@x, CASE WHEN @brPosPrev = 0 THEN @brPosPrev ELSE @brPosPrev END, @brPos-CASE WHEN @brPosPrev = 0 THEN @brPosPrev ELSE @brPosPrev END+4)

  SET @srcPos = (SELECT CHARINDEX('src="files/', @xPart))+11;
  SET @JPGPos = (SELECT CHARINDEX('.JPG', @xPart));
  SET @SlashPos = (SELECT CHARINDEX('/', @xPart, @srcPos));

  IF (@JPGPos < @SlashPos OR @SlashPos = 0)
    SET @xPart = REPLACE(@xPart, 'src="files/', 'src="files/new/');

  SET @y = @y + @xPart;

  SET @brPosPrev = @brPos + 4;
  SET @brPos = (SELECT CHARINDEX('</P>', @x, @brPosPrev));

END

SELECT @y

The Patterns I used in the CHARINDEX calls can be moved to variables as well - so you are not bound to use .JPG or whatever.