Replace img src from a Html column in Sql Sqrver

问题:

Is there any way to replace img src from a Html column in Sql Sqrver? for example, SELECT TOP 1 html FROM TableName result in

<p><img id="img" title="imgtitle" src="/source1.jpg" width="225" height="300" /> text</p>

where I need to update this by replacing /source1.jpg to /source2.jpg

source1 can be any string dynamically (i.e first I need to find it in the html column using query).

<p><img id="img" title="imgtitle" src="/source2.jpg" width="225" height="300" /> text</p>

There is one thing in my mind is using regex but I have no idea how to use regex in SQL server and how to update it using that.

is anyone have any idea how to do this?

回答1:

You an use the following query to achieve your goal:

DECLARE @SRC VARCHAR(MAX) = '<P><IMG ID="IMG" TITLE="IMGTITLE" SRC="/SOURCE1.JPG" WIDTH="225" HEIGHT="300" /> TEXT</P>';

DECLARE @SUB VARCHAR(MAX) = RIGHT(@SRC, (LEN(@SRC)-PATINDEX ( '%SRC="%' , @SRC )-5));

DECLARE @LEN INT = CHARINDEX ( '"' , @SUB );

SELECT REPLACE(@SRC,SUBSTRING(@SRC,(PATINDEX ( '%SRC="%' , @SRC )+5),@LEN),'/NEWVALUE.JPG');

回答2:

You could use xml features:

DECLARE @t TABLE (RecordXML XML);
Declare @xml XML
SET @xml = '<p>
  <img id="img" title="imgtitle" src="/source1.jpg" width="225" height="300" />
  text
  </p>'
INSERT @t VALUES (@xml);

DECLARE @value nvarchar(50)  = '/source2.jpg'
DECLARE @oldvalue nvarchar(50) = '/source1.jpg'

UPDATE
  @t
SET
   RecordXML.modify('replace value of 
   (/p/img/@src[.=sql:variable("@oldvalue")])[1] with sql:variable("@value")')
WHERE
  RecordXML.exist('/p/img[@src=sql:variable("@oldvalue")]') = 1;


SELECT * FROM @t;

I think that is more accurate than using the REPLACE function

edit:

try this query, it will change the value using two columns (old and new value), or give me a sample of data :)

DECLARE @t TABLE (RecordXML XML, oldvalue nvarchar(100), value nvarchar(100));
DECLARE @xml1 XML = '<p><img id="img" title="imgtitle" src="/source1.jpg" width="225" height="300" />text</p>';
DECLARE @xml2 XML = '<p><img id="img" title="imgtitle" src="/source5.jpg" width="225" height="300" />text</p>';


INSERT @t VALUES (@xml1, '/source1.jpg', '/source2.jpg');
INSERT @t VALUES (@xml2, '/source5.jpg', '/sourceabc.jpg');

UPDATE
  @t
SET
   RecordXML.modify('replace value of
   (/p/img/@src[.=sql:column("oldvalue")])[1] with sql:column("value")')
WHERE
  RecordXML.exist('/p/img[@src=sql:column("oldvalue")]') = 1;


SELECT RecordXML FROM @t;

edit: or if you don't know the old value you can change the update query:

UPDATE
  @t
SET
   RecordXML.modify('replace value of
   (/p/img/@src)[1] with sql:column("value")');

回答3:

You could just use the replace function in sql server:

declare @valFrom  nvarchar
declare @valTo  nvarchar

set @valFrom = '/source1.jpg'
set @valTo = '/source2.jpg'

update TableName
  set html = replace(html, @valFrom, @valTo)

Set @valFrom and @valTo to whatever you like.It might be worthwhile setting them as:

set @valFrom = 'src="/source1.jpg"'
set @valTo = 'src="/source2.jpg"'

This will make sure you're actually only changing the src.

If your field type is xml you may need to do some casting:

declare @valFrom  varchar(max)
declare @valTo  varchar(max)

set @valFrom = '/source1.jpg'
set @valTo = '/source2.jpg'

update TableName
  set html = replace(cast(html as varchar(max)), @valFrom, @valTo)