标量值函数及调用的详细解析！

Question

USE [mandala]
GO

/****** Object: UserDefinedFunction [dbo].[SplitString] Script Date: 2018/1/2 14:37:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].SplitString
)
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @rslt VARCHAR(50) ,
@SubStr CHAR(1) ,
@i INTEGER ,
@iTempPos INTEGER
SET @SubStr = '|'
IF @iPos < 1
RETURN NULL
IF SUBSTRING(@SourceString, 1, 1) <> '|' --/如果SourceString第一个并不是'|
SET @SourceString = @SubStr + @SourceString

    SET @i = 1

    WHILE @i < @iPos + 1 
        BEGIN
            SET @iTempPos = CHARINDEX(@SubStr, @SourceString, 1) 
            IF @iTempPos < 1 
                RETURN NULL
            SET @SourceString = RTRIM(SUBSTRING(@SourceString,
                                                @iTempPos + 1,
                                                LEN(@SourceString)))
            SET @i = @i + 1
        END
    SET @iTempPos = CHARINDEX(@SubStr, @SourceString, 1)
    IF @iTempPos < 1 
        SET @rslt = @SourceString
    ELSE 
        SET @rslt = SUBSTRING(@SourceString, 1, @iTempPos - 1)  
    RETURN  @rslt

END

GO

2.调用：
select
,case when mandala.dbo.SplitString(1,a.HISstudyID)<>'' then
substring(mandala.dbo.SplitString(1,a.HISstudyID),3,len(mandala.dbo.SplitString(1,a.HISstudyID))-2)
else '' end orderid
from ba a

Answer 1

http://blog.csdn.net/leftfist/article/details/4766149