USE [TEMPDB]
GO
/****** Object: UserDefinedFunction [dbo].[FindStr] Script Date: 2017/7/28 下午 04:24:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: 20150212
-- Description: for Split Str
-- select dbo.FindStr('{"FlowStepBtnID":"B20_BtnClose","FlowStepBtnCaption":"結案","FlowStepBtnDesc":"","FlowStepBtnConfirmMsg":"","FlowStepBtnNextStepID":"Z00","FlowStepBtnNextStepName":"正常結案","FlowStepBtnIsNeedOpinion":"N","FlowStepBtnIsMultiSelect":"N","FlowStepBtnIsSendMail":"Y","FlowStepBtnNextStepDealCondition":"END","FlowStepBtnIsAddMultiSubFlow":"N","FlowStepBtnAddSubFlowID":"","FlowStepBtnAddSubFlowStepBtnID":"","FlowStepBtnAddSubFlowStepBtnAssignToList":"","FlowStepBtnChkSubFlowList":"","FlowStepBtnUpdCustIDList":"SetCloseFlow","FlowStepBtnAssignToList":"{\"CFPMain.B20\":\"主流程處理人員\"}","FlowNativeStepBtnCaption":"結案","FlowNativeStepBtnNextStepName":"正常結案"}','FlowStepBtnID','','')
##得到 B20_BtnClose
-- select dbo.FindStr('{"FlowStepBtnID":"B20_BtnClose","FlowStepBtnCaption":"結案","FlowStepBtnDesc":"","FlowStepBtnConfirmMsg":"","FlowStepBtnNextStepID":"Z00","FlowStepBtnNextStepName":"正常結案","FlowStepBtnIsNeedOpinion":"N","FlowStepBtnIsMultiSelect":"N","FlowStepBtnIsSendMail":"Y","FlowStepBtnNextStepDealCondition":"END","FlowStepBtnIsAddMultiSubFlow":"N","FlowStepBtnAddSubFlowID":"",
"FlowStepBtnAddSubFlowStepBtnID":"","FlowStepBtnAddSubFlowStepBtnAssignToList":"","FlowStepBtnChkSubFlowList":"","FlowStepBtnUpdCustIDList":"SetCloseFlow","FlowStepBtnAssignToList":"{\"CFPMain.B20\":\"主流程處理人員\"}","FlowNativeStepBtnCaption":"結案","FlowNativeStepBtnNextStepName":"正常結案"}','FlowStepBtnAssignToList','\','')
##得到 {"CFPMain.B20":"主流程處理人員"}
-- select dbo.FindStr('{"FlowStepBtnID":"B20_BtnClose","FlowStepBtnCaption":"結案","FlowStepBtnDesc":"","FlowStepBtnConfirmMsg":"","FlowStepBtnNextStepID":"Z00","FlowStepBtnNextStepName":"正常結案","FlowStepBtnIsNeedOpinion":"N","FlowStepBtnIsMultiSelect":"N","FlowStepBtnIsSendMail":"Y","FlowStepBtnNextStepDealCondition":"END","FlowStepBtnIsAddMultiSubFlow":"N","FlowStepBtnAddSubFlowID":"","FlowStepBtnAddSubFlowStepBtnID":"","FlowStepBtnAddSubFlowStepBtnAssignToList":"","FlowStepBtnChkSubFlowList":"","FlowStepBtnUpdCustIDList":"SetCloseFlow","FlowStepBtnAssignToList":"{\"CFPMain.B20\":\"主流程處理人員\"}","FlowNativeStepBtnCaption":"結案","FlowNativeStepBtnNextStepName":"正常結案"}','FlowNativeStepBtnNextStepName','','')
##得到 正常結案
-- =============================================
ALTER FUNCTION [dbo].[FindStr]
(
@InputStr VARCHAR(MAX), --字串
@FindStr VARCHAR(50), --尋找字串'str'
@ReplaceB VARCHAR(50), --是否有置換的值
@ReplaceA VARCHAR(50) --是否有置換的值
)
RETURNS VARCHAR(500)
AS
BEGIN
DECLARE @RETURNSTR varchar(MAX)
DECLARE @SplitStr VARCHAR(MAX) --程式中拆解後的字串
DECLARE @OriSplitStr VARCHAR(MAX) --保留原始的字串，以利重新再判斷
Declare @NextString varchar(500)
Declare @Pos int
Declare @FindStrPos int
Declare @EndPos int
Declare @NextEndPos int
Declare @Delimiter1 CHAR(1)
Declare @Delimiter2 CHAR(1)
Declare @Delimiter3 CHAR(1)
Declare @Delimiter4 CHAR(1)
SET @Delimiter1 = ','
SET @Delimiter2 = ':'
SET @Delimiter3 = '{'
SET @Delimiter4 = '}'
SET @RETURNSTR = '' --回傳的資料
SET @OriSplitStr = @InputStr
SET @OriSplitStr=substring(@OriSplitStr,2,len(@OriSplitStr)-2) --頭尾的{}拿掉
SET @OriSplitStr = @OriSplitStr + ',' --字串最後加上','才能取到最後一個字串
SET @FindStr = '"' + @FindStr + '"'
SET @FindStrPos = charindex(@FindStr, @OriSplitStr,1) --確認是否有該字串的存在,找不到回傳NULL
--若存在從那個字串開始後找到","拆解出第一個字串
if @FindStrPos > 0
Begin
SET @EndPos = charindex(@Delimiter1, @OriSplitStr,@FindStrPos) --若從那個字串開始後找到","拆解出第一個字串
SET @SplitStr = substring(@OriSplitStr,@FindStrPos,@EndPos-@FindStrPos) --從該字串開始取，直到
--SET @RETURNSTR += ' @FindStrPos = ' + cast(@FindStrPos as varchar(10)) + ' @EndPos = ' + cast(@EndPos as varchar(10))
--SET @RETURNSTR += ' @SplitStr = ' + @SplitStr
--針對有{}裡也有,號的拆出的資料不完整，故要重新判斷後再重新拆解
SET @NextEndPos = charindex(@Delimiter3,@SplitStr,1)
--SET @RETURNSTR = ' @SplitStr = ' + cast(@SplitStr as varchar(100)) + ' @Delimiter3 = ' + cast(@Delimiter3 as varchar(100)) + ' @NextEndPos = ' + cast(@NextEndPos as varchar(100))
if @NextEndPos > 0
Begin
SET @EndPos = charindex(@Delimiter4, @OriSplitStr,@FindStrPos) --若從那個字串開始後找到"}"拆解出第一個字串
SET @SplitStr = substring(@OriSplitStr,@FindStrPos,@EndPos-@FindStrPos + 2) --從該字串開始取，直到
--SET @RETURNSTR += ' @SplitStr = ' + cast(@SplitStr as varchar(500)) + ' @FindStrPos = ' + cast(@FindStrPos as varchar(100)) + ' @EndPos = ' + cast(@EndPos as varchar(100))
END
SET @EndPos = charindex(@Delimiter2, @SplitStr,1) --若從那個字串開始後找到":"拆解出第一個字串
SET @SplitStr = substring(@SplitStr,@EndPos+1,len(@SplitStr)- @EndPos) --從該字串開始取，直到
--SET @RETURNSTR+= ' @EndPos = ' + cast(@EndPos as varchar(10))
--SET @RETURNSTR+= ' @SplitStr = ' + @SplitStr
SET @SplitStr=substring(@SplitStr,2,len(@SplitStr)-2) --頭尾的""拿掉
SET @RETURNSTR= @SplitStr
End
IF (@ReplaceB <> '') --若有帶入replace的變數，則要replace特定的字元
BEGIN
SELECT @SplitStr=REPLACE(@SplitStr,@ReplaceB,@ReplaceA)
SET @RETURNSTR= @SplitStr
END
RETURN @RETURNSTR
END
留言列表
