超疑难 字符串解析
有这么一个字符串，if(4/(if(2>1,2,1)+2) = 0,if(6/(3+3)=1,3,2),4/(4+4))
，怎么把他里面的 和if左括号 匹配的 右小括号 都变成 右中括号。
处理前：if(4/(if(2>1,2,1)+2) = 0,if(6/(3+3)=1,3,2),4/(4+4))
处理后：if(4/(if(2>1,2,1]+2) = 0,if(6/(3+3)=1,3,2],4/(4+4)]
项目里很重要的一个功能，各位高手帮帮忙啊，谢了
sql?字符串处理?
[解决办法]
--仅供参考啊，汗～，期待好的算法……
--还是用程序实现比较方便。用CLR写个函数，放到SQL SERVER里来处理吧，最简单。
DROP TABLE #temp
CREATE TABLE #temp(id INT, expression VARCHAR(MAX))
INSERT #temp values(1, 'if(4/(if(2>1,2,1)+2) = 0,if(6/(3+3)=1,3,2),4/(4+4))')
--SQL
;WITH cte1 AS
(
SELECT
id,
pos = CHARINDEX('(', expression),
word = CAST(LEFT(expression, CHARINDEX('(', expression) - 1) AS NVARCHAR(MAX)),
name = 1,
Split = CAST(STUFF(expression + '(', 1, CHARINDEX('(', expression + '('), '') AS NVARCHAR(MAX))
FROM #temp
UNION ALL
SELECT
id,
pos = a.pos + CHARINDEX('(', Split),
word = CAST(LEFT(Split, CHARINDEX('(', Split) - 1) AS NVARCHAR(MAX)),
name = 1,
Split = CAST(STUFF(Split, 1, CHARINDEX('(', Split), '') AS NVARCHAR(MAX))
FROM cte1 a
WHERE Split > ''
),
cte2 AS
(
SELECT
id,
pos = CHARINDEX(')', expression),
word = CAST(LEFT(expression, CHARINDEX(')', expression) - 1) AS NVARCHAR(MAX)),
name = -1,
Split = CAST(STUFF(expression + ')', 1, CHARINDEX(')', expression + ')'), '') AS NVARCHAR(MAX))
FROM #temp
UNION ALL
SELECT
id,
pos = a.pos + CHARINDEX(')', Split),
word = CAST(LEFT(Split, CHARINDEX(')', Split) - 1) AS NVARCHAR(MAX)),
name = -1,
Split = CAST(STUFF(Split, 1, CHARINDEX(')', Split), '') AS NVARCHAR(MAX))
FROM cte2 a
WHERE Split > ''
),
cte3 AS
(
SELECT rowno = ROW_NUMBER() OVER(ORDER BY id, pos), * FROM
(
SELECT id,pos,word,name FROM cte1 WHERE pos < 52
UNION ALL
SELECT id,pos,word,name FROM cte2 WHERE pos < 52
) t
),
cte4 AS
(
SELECT rowno, id,pos,word,name FROM cte3 WHERE LEN(word) >= 2 AND UPPER(RIGHT(word, 2))='IF'
UNION ALL
SELECT b.rowno, b.id,a.pos,b.word,name=a.name+b.name
FROM cte4 a
INNER JOIN cte3 b
ON a.rowno = b.rowno-1
WHERE a.name+b.name > 0
)
SELECT RESULT=REPLACE(
(
SELECT A.WORD + (CASE WHEN a.name=1 THEN '(' WHEN A.NAME = -1 AND b.rowno IS NOT NULL THEN ']' ELSE ')' end)
FROM cte3 a
LEFT JOIN
(
SELECT id, pos, rowno = MAX(rowno)+1
FROM cte4
group BY id, pos
) b
ON a.rowno = b.rowno
WHERE a.name = -1
ORDER BY a.id, a.pos
FOR XML PATH('')
),
'>', '>'
)
/*
RESULT
if(4/(if(2>1,2,1]+2) = 0,if(6/(3+3)=1,3,2],4/(4+4)]
*/
--第一步：创建自定义函数
create function [dbo].[test_if]
(@STR VARCHAR(MAX))
returns varchar(max)
as
begin
DECLARE @cu1 TABLE (SName varchar(1000),Number int,LR CHAR(1),re int ,number_R int,fz BIT)
DECLARE @cu2 TABLE (number_R int,re int)
INSERT @cu1
SELECT *,ROW_NUMBER() OVER(ORDER BY NUMBER) RE
,CAST(0 AS int) number_R
,CAST(0 AS BIT) fz
FROM
(
SELECT SName=SUBSTRING(a.SName,b.Number,Charindex('(',a.SName+'(',b.Number)-b.Number),
b.Number-1 Number,'L' LR
FROM (SELECT SName=@STR)a,master.dbo.spt_values b
Where SUBSTRING('('+a.SName,b.Number,1)='(' and b.Type='P'
UNION ALL
SELECT SName=SUBSTRING(a.SName,b.Number,Charindex(')',a.SName+')',b.Number)-b.Number),
Charindex(')',a.SName+')',b.Number) Number,'R'
FROM (SELECT SName=@STR)a,master.dbo.spt_values b
Where SUBSTRING(')'+a.SName,b.Number,1)=')' and b.Type='P'
) a
DECLARE @number_R int,@re_R int,@number_L int,@re_L int
WHILE EXISTS(SELECT 1 FROM @cu1 WHERE fz=0)
BEGIN
SELECT TOP 1 @number_R=number,@re_R=re FROM @cu1 WHERE lr='R' AND fz=0 ORDER BY re ASC
SELECT TOP 1 @number_L=number,@re_L=re FROM @cu1 WHERE lr='L' AND fz=0 AND re<@re_R ORDER BY re DESC
UPDATE @cu1 SET fz=1 WHERE re IN (@re_R,@re_L)
UPDATE @cu1 SET number_R=@number_R WHERE re=@re_L
END
;
WITH a1 AS
(
SELECT SName,number_R,ROW_NUMBER() OVER(ORDER BY re) re
FROM @cu1
WHERE lr='L'
)
INSERT @cu2
SELECT number_R,ROW_NUMBER() OVER(ORDER BY re) re
FROM a1
WHERE re IN (SELECT re+1 re FROM a1 WHERE CHARINDEX('IF',UPPER(SName))>0)
DECLARE @i1 int,@i2 INT
SELECT @i1=1,@i2=MAX(re) FROM @cu2
WHILE @i1<=@i2
BEGIN
SELECT @number_R=number_R FROM @cu2 WHERE re=@i1
SET @STR=STUFF(@STR,@number_R,1,']')
SET @i1=@i1+1
END
return @STR
end
--第二步：执行自定义函数
SELECT dbo.test_if('if(4/(if(2>1,2,1)+2) = 0,if(6/(3+3)=1,3,2),4/(4+4))')
