DROP FUNCTION [SplitToTable]
GO
Create FUNCTION [dbo].[SplitToTable]
(
@SplitString nvarchar(max),
@Separator nvarchar(10)=' '
)
RETURNS @SplitStringsTable TABLE
(
[id] int identity(1,1),
[value] nvarchar(max)
)
AS
BEGIN
DECLARE @CurrentIndex int;
DECLARE @NextIndex int;
DECLARE @ReturnText nvarchar(max);
SELECT @CurrentIndex=1;
WHILE(@CurrentIndex<=len(@SplitString))
BEGIN
SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);
IF(@NextIndex=0 OR @NextIndex IS NULL)
SELECT @NextIndex=len(@SplitString)+1;
SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);
INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText);
SELECT @CurrentIndex=@NextIndex+1;
END
RETURN;
END
GO
DROP FUNCTION GetCaseCode
GO
CREATE FUNCTION GetCaseCode(@FullCaseCode varchar(200))
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @CaseCode varchar(10) = ''
DECLARE @Tbl TABLE(ID INT,String varchar(10))
DECLARE @TempCaseCode varchar(10)
DECLARE @ID INTDECLARE @TEMP VARCHAR(10)
INSERT INTO @Tbl SELECT ID,VALUE FROM dbo.[SplitToTable](@FullCaseCode,',')
SELECT @ID = ID,@TempCaseCode = STRING FROM @Tbl
WHERE CHARINDEX('$',@TempCaseCode) = 1 OR LEN(@TempCaseCode) <> 6
BEGIN
DELETE FROM @Tbl WHERE ID = @ID
SELECT @ID = ID,@TempCaseCode = STRING FROM @Tbl
END
SET @CaseCode = @TempCaseCode
RETURN @CaseCode
END
注意:SELECT @ID = ID,@TempCaseCode = STRING FROM @Tbl 有两条
问题是:
为什么第一条没有值而第二条却有值?
现有字段存入字符如:
104233,104257,$111610,$111618
要求取到逗号分割出来的字符串中最后一个没有$字符的字符串.
使用上面的第二种方法即可,但两个函数都必须创建才可,因为第二个要借用第一个来处理逗号字符串.
DECLARE @FullStr VARCHAR(100)
SET @FullStr = '104233,104257,$111610,$111618'
SELECT DBO.GetCaseCode(@FullStr)--结果
104257