27,582
社区成员




IF OBJECT_ID('fn_split','TF') IS NOT NULL
DROP FUNCTION [dbo].[fn_split]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--分拆字符串
CREATE FUNCTION [dbo].[fn_split]
(@splitstring NVARCHAR(max),
@separator CHAR(1) = ',')
RETURNS @splitstringstable TABLE ([item] NVARCHAR(200))
AS
BEGIN
DECLARE @currentindex INT
DECLARE @nextindex INT
DECLARE @returntext NVARCHAR(200)
SELECT @currentindex=1
WHILE(@currentindex<=datalength(@splitstring)/2)
BEGIN
SELECT @nextindex=charindex(@separator,@splitstring,@currentindex)
IF(@nextindex=0 OR @nextindex IS NULL)
SELECT @nextindex=datalength(@splitstring)/2+1
SELECT @returntext=substring(@splitstring,@currentindex,@nextindex-@currentindex)
INSERT INTO @splitstringstable([item])
VALUES(@returntext)
SELECT @currentindex=@nextindex+1
END
RETURN
END
GO
-- 参数形式
DECLARE @subj_name nvarchar(max)
SET @subj_name = 'A+B+C-E-F*D/F'
SET @subj_name = REPLACE(REPLACE(REPLACE(REPLACE(@subj_name,'+',',+'),'-',',-'),'*',',*'),'/',',/')
SET @subj_name = '+' + @subj_name
SELECT LEFT([item],1) a,RIGHT([item],LEN([item]) - 1) b
FROM dbo.[fn_split](@subj_name,',')
a b
---- ---
+ A
+ B
+ C
- E
- F
* D
/ F
-- 表关联
IF OBJECT_ID('ht_check_report_design','U') IS NOT NULL
DROP TABLE ht_check_report_design
CREATE TABLE ht_check_report_design(subj_name varchar(128))
INSERT INTO ht_check_report_design(subj_name)
SELECT 'A+B+C-E-F*D/F' UNION ALL
SELECT '80+80+70-10-50*2/3'
SELECT subj_name,LEFT([item],1) a,RIGHT([item],LEN([item]) - 1) b
FROM ht_check_report_design A
CROSS APPLY dbo.[fn_split]('+' +REPLACE(REPLACE(REPLACE(REPLACE(subj_name,'+',',+'),'-',',-'),'*',',*'),'/',',/'),',')
--
IF OBJECT_ID('f_splitSTR') IS NOT NULL
DROP FUNCTION f_splitSTR
go
CREATE FUNCTION f_splitSTR (@s VARCHAR(8000))
RETURNS @re TABLE
(
split VARCHAR(10) ,
value VARCHAR(100)
)
AS
BEGIN
DECLARE @splits TABLE
(
split VARCHAR(10) ,
splitLen AS LEN(split)
)
INSERT @splits (split)
SELECT '+'
UNION ALL
SELECT '-'
UNION ALL
SELECT '*'
UNION ALL
SELECT '/'
DECLARE @pos1 INT ,
@pos2 INT ,
@split VARCHAR(10) ,
@splitLen INT
SELECT TOP 1
@pos1=1 , @split=split , @splitLen=splitLen
FROM @splits
WHERE @s LIKE split+'%'
WHILE @pos1 > 0
BEGIN
SELECT TOP 1
@pos2=CHARINDEX(split , @s , @splitLen+1)
FROM @splits
WHERE CHARINDEX(split , @s , @splitLen+1) > 0
ORDER BY CHARINDEX(split , @s , @splitLen+1)
IF @@rowcount = 0
BEGIN
INSERT @re
VALUES (@split , STUFF(@s , 1 , @splitLen , ''))
RETURN
END
ELSE
BEGIN
INSERT @re
VALUES (@split , SUBSTRING(@s , @splitLen+1 , @pos2-@splitLen-1))
SELECT TOP 1
@pos1=1 , @split=split , @splitLen=splitLen , @s=STUFF(@s , 1 , @pos2-1 , '')
FROM @splits
WHERE STUFF(@s , 1 , @pos2-1 , '') LIKE split+'%'
END
END
RETURN
END
go
--
DECLARE @str VARCHAR(100)
SET @str='A+B+C-E-F*D/F'
SET @str='+'+@str
SELECT * FROM dbo.f_splitSTR(@str)
/*
split value
+ A
+ B
+ C
- E
- F
* D
/ F
*/