34,838
社区成员




declare @v nvarchar(999)=N'5515,1,<t>宝贝,想你了哦,最近一直很难过</t>,<t>快乐点,没有忧愁</t>aaaaa,kkkkk';
SET @v='<t str="'+REPLACE(REPLACE(@v,',<t>','"/><t>'),'</t>','</t><t str="')+'"/>'
SELECT STUFF((
SELECT ''','''+a.p FROM (VALUES(CONVERT(XML,'<n>'+@v+'</n>')))c(x)
OUTER APPLY (SELECT ISNULL(REPLACE(x.n.value('@str','nvarchar(max)'),',',''','''),'') +x.n.value('.','nvarchar(max)') AS p FROM c.x.nodes('n/t') x(n)) AS a
WHERE LEN(a.p)>0
FOR XML PATH('')
),1,2,'')+''''
'5515','1','宝贝,想你了哦,最近一直很难过','快乐点,没有忧愁','aaaaa','kkkkk'
CREATE FUNCTION fun_replace
(
@str nvarchar(max) --源字符串
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @ReturnText nvarchar(max)='';
WHILE(CHARINDEX('<t>',@str)>0)
BEGIN
SET @ReturnText=@ReturnText+replace(SUBSTRING(@str,0,CHARINDEX('<t>',@str)),',',''',''')
SET @ReturnText=@ReturnText+SUBSTRING(@str,CHARINDEX('<t>',@str)+3,CHARINDEX('</t>',@str)-CHARINDEX('<t>',@str)-3)
SET @str=STUFF(@str,1,CHARINDEX('</t>',@str)+3,'')
END
RETURN @ReturnText;
END
GO
declare @v nvarchar(999)='5515,1,<t>宝贝,想你了哦,最近一直很难过</t>,<t>快乐点,没有忧愁</t>';
declare @s int=charindex('<t>',@v);
set @v=''''+dbo.fun_replace(@v)+''''
print @v
IF OBJECT_ID('dbo.Fun_ReplaceTag') IS NOT NULL
DROP FUNCTION dbo.Fun_ReplaceTag
GO
-- =============================================
-- Author: yenange
-- Create date: 2017-12-09
-- Description: 替换字符串中的标签
-- =============================================
CREATE FUNCTION dbo.Fun_ReplaceTag
(
@s NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @r NVARCHAR(MAX),@singleChar NCHAR(1),@flag BIT
SET @s=ISNULL(@s,'')
SET @r=''
SET @flag=0
WHILE len(@s)>0
BEGIN
SET @singleChar=LEFT(@s,1)
SET @s=SUBSTRING(@s,2,len(@s)-1)
IF @singleChar='<'
BEGIN
SET @flag=1
END
IF @flag=0
BEGIN
SET @r=@r+@singleChar
END
IF @singleChar='>'
BEGIN
SET @flag=0
END
END
RETURN @r;
END
GO
declare @v nvarchar(999)
SET @v='5515,1,<t>宝贝,想你了哦,最近一直很难过</t>,<t>快乐点,没有忧愁</t>';
PRINT dbo.Fun_ReplaceTag(@v)
/*
5515,1,宝贝,想你了哦,最近一直很难过,快乐点,没有忧愁
*/
效率最高的办法, 是写 sqlclr 函数, 用正则替换的方法来完成, 但要花的功夫也不少了。
如果要求不高就按上面吧。