--提取中文 IF OBJECT_ID('DBO.CHINA_STR') IS NOT NULL DROP FUNCTION DBO.CHINA_STR GO CREATE FUNCTION DBO.CHINA_STR(@S NVARCHAR(100)) RETURNS VARCHAR(100) AS BEGIN WHILE PATINDEX('%[^吖-座]%',@S) > 0 SET @S = STUFF(@S,PATINDEX('%[^吖-座]%',@S),1,N'') RETURN @S END GO PRINT DBO.CHINA_STR('呵呵ABC123ABC') GO /* 呵呵
--提取中文
IF OBJECT_ID('DBO.CHINA_STR') IS NOT NULL
DROP FUNCTION DBO.CHINA_STR
GO
CREATE FUNCTION DBO.CHINA_STR(@S NVARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^吖-座]%',@S) > 0
SET @S = STUFF(@S,PATINDEX('%[^吖-座]%',@S),1,N'')
RETURN @S
END
GO
PRINT DBO.CHINA_STR('呵呵ABC123ABC')
GO /*
呵呵
declare @str varchar(100)='<img width="521" height="178" alt="" src="/UploadFiles/image/ss.jpg" />'select substring(@str,charindex('src="',@str)+5,charindex('.jpg"',@str)-charindex('src="',@str)-1)/*-------------------------
/UploadFiles/image/ss.jpg
declare @str varchar(100)='<img width="521" height="178" alt="" src="/UploadFiles/image/ss.jpg" />'select STUFF(@str,charindex('src="',@str),charindex('.jpg"',@str)-charindex('src="',@str)+5,'')/*----------------------------------------
<img width="521" height="178" alt="" />
文字文字文字文字文字<img width="521" height="178" alt="" src="/UploadFiles/image/ss.jpg" />文字文字文字文字文字文字文字文字文字也就是图片两边都有文字、也只取出文字就行不要图片
declare @str varchar(100)='你好<img width="521" height="178" alt="" src="/UploadFiles/image/ss.jpg" />我勒个去,哈哈'select STUFF(@str,CHARINDEX('<',@str),CHARINDEX('>',@str)-CHARINDEX('<',@str)+1,'')/*------------------
你好我勒个去,哈哈(1 行受影响)