请教一存储过程 字符拆分的问题
@express 为计算表达式中的数据
数据类型如下 A+B+C-E-F*D/F.....之类的数据 请问该如何取出各种符号中间的数据 谢谢
CREATE PROCEDURE dt_guestroom_income (@p1 char(2) output,@p2 datetime)
AS
declare @line_y char(3) --列号
declare @subj_name varchar(64)--科目名称
declare @today_income numeric --当天收入
declare @lastyear_today_income numeric --去年同日
declare @month_income numeric --本月收入
declare @year_income numeric --本年收入
declare @last_time numeric --去年同期
declare @growth_rate numeric --增长率
declare @express char(128) --计算表达式
declare @check char(1) --符号表达式
declare @i int--位置记数
--declare @pos int --符号位置
declare @subj_code varchar(16)
--从科目基础表中查出相应的科目行号,科目名称
delete from ht_test_table1
--从表ht_check_report_design中取科目名称和计算表达式
Declare Cur_tmp Cursor For
select subj_name,express
from ht_check_report_design
where code =@p1
open Cur_tmp
Fetch Next From Cur_tmp into @subj_name,@express
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Cur_tmp into @subj_name,@express
--判断是@express是否有＋,－,＊,／,h 等符号
--若为+号则
if ( CHARINDEX( '+ ', @express,1)> 0)
----做加法处理 发现+号 拆分前面和后面的数据
SET @i=1
WHILE @i <=LEN(@express)
BEGIN
if ( SUBSTRING (@express,@i,1) = '+ ')
--这里需要拆分标量@express 取出加号和前一个符号前的代码
SET @i=@i+1
END
--若为-号则
if(CHARINDEX( '- ',@express,1)> 0)
---做减法处理
--若为*号则
if(CHARINDEX( '* ',@express,1)> 0)
---做乘法处理
--若为/号则
if(CHARINDEX( '/ ',@express,1)> 0)
---做除法处理
--若为h则
if(CHARINDEX( 'h ',@express,1)> 0)
print @subj_name + '-- '+@express
--做h的处理
END
CLOSE Cur_tmp
Deallocate Cur_tmp
--从科目汇总表ht_cash_subj_rep中按科目代码取出符合条件数据
GO
很长的
就像上面的
结果为 A+B+C-E-F*D/F
a b
+ A
+ B
+ C
- E
- F
* D
/ F
[最优解释]
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)
/*
splitvalue
+A
+B
+C
-E
-F
*D
/F
*/
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,'+',',+'),'-',',-'),'*',',*'),'/',',/'),',')
--