提取SQL字段文章内容里的第一张图片，截取字符串

 

--update  dt_channel_article_news02 SET img_url='/upload/202012/21/1.png'  --批量更改图片路径
	
--查出内容里的第一张图.JPG格式的
	SELECT * FROM
	(
	SELECT  1 AS a
	--,content,CHARINDEX('upload',content)-1
	--,(CHARINDEX('.jpg',SUBSTRING(content,CHARINDEX('upload',content)-1,99999))+4-CHARINDEX('upload',content)-1+2)
	--,CHARINDEX('.jpg',SUBSTRING(content,CHARINDEX('upload',content)-1,99999))+4
	--,SUBSTRING(content,CHARINDEX('upload',content)-1,99999)
	,SUBSTRING(content,CHARINDEX('/upload',content),CHARINDEX('.jpg',SUBSTRING(content,CHARINDEX('upload',content)-1,99999))+3) AS b
	,LEN(SUBSTRING(content,CHARINDEX('/upload',content),CHARINDEX('.jpg',SUBSTRING(content,CHARINDEX('upload',content)-1,99999))+3)) AS n
	--,SUBSTRING(content,CHARINDEX('upload',content)-1,(CHARINDEX('.jpg',content)+4-CHARINDEX('upload',content)-1+2)) 
	,*
	FROM dt_channel_article_news02
	WHERE 1=1
	--AND id=99099
	--AND content LIKE '%upload%'
	--AND content LIKE '%.jpg%'
	AND CHARINDEX('/upload',content)>0
	AND CHARINDEX('.jpg',content)>0
	AND LEN(SUBSTRING(content,CHARINDEX('/upload',content),CHARINDEX('.jpg',SUBSTRING(content,CHARINDEX('upload',content)-1,99999))+3))<82
	
	)t1 ORDER BY t1.n DESC
	
	
--以上条件批量把内容的第一张图放别的地方
	update  dt_channel_article_news02 SET img_url=SUBSTRING(content,CHARINDEX('/upload',content),CHARINDEX('.jpg',SUBSTRING(content,CHARINDEX('upload',content)-1,99999))+3)
	WHERE 1=1
	--AND id=99099
	--AND content LIKE '%upload%'
	--AND content LIKE '%.jpg%'
	AND CHARINDEX('/upload',content)>0
	AND CHARINDEX('.jpg',content)>0
	AND LEN(SUBSTRING(content,CHARINDEX('/upload',content),CHARINDEX('.jpg',SUBSTRING(content,CHARINDEX('upload',content)-1,99999))+3))<82