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