字符串分割大全及实例
1. 分割后按顺序取数据
/* ---------------------------------------------------
fnMain_GetStrSegment (SQL_SCALAR_FUNCTION)
Description: Extracts a specific segment of a string, given the delimiter
Usage remarks / example:
e.g. Input: 'A•B•C' Delimiter: '•'
A = Segment 1
B = Segment 2
C = Segment 3
SELECT dbo.fnMain_GetStrSegment('A•B•C','•',2); --This should return 'B'
---------------------------------------------------*/
BEGIN TRY DROP FUNCTION [dbo].[fnMain_GetStrSegment] END TRY BEGIN CATCH END CATCH
GO
CREATE FUNCTION [dbo].[fnMain_GetStrSegment](@InputStr nvarchar(max), @Delimiter nvarchar(255), @SegmentNo int) RETURNS nvarchar(max) AS
BEGIN
DECLARE @Result nvarchar(max),@StartPos int,@EndPos int,@Cnt int,@DelimLen tinyint;
SELECT @Result='',@Cnt=1,@DelimLen=CASE WHEN @Delimiter=N' ' THEN 1 ELSE LEN(@Delimiter) END;
WHILE @Cnt<=@SegmentNo
BEGIN
IF @Cnt=1
BEGIN SET @StartPos=1 END
ELSE BEGIN
SET @StartPos=ISNULL(CHARINDEX(@Delimiter,@InputStr,@StartPos),0)+@DelimLen;
IF @StartPos<=1 RETURN '';
END
SET @EndPos=ISNULL(CHARINDEX(@Delimiter,@InputStr,@StartPos),0);
IF @EndPos<@StartPos SET @EndPos=LEN(@InputStr)+@DelimLen;
SET @Cnt=@Cnt+1;
END
IF @StartPos>0 AND @EndPos>@StartPos SET @Result=SUBSTRING(@InputStr,@StartPos,@EndPos-@StartPos);
RETURN @Result;
END
GO
-------For Example --SELECT dbo.fnMain_GetStrSegment('1|4|5',N'|',2)
2. 分割成整形表
/* ---------------------------------------------------
tfMain_StrToTblInt (SQL_TABLE_VALUED_FUNCTION)
Description: Function to convert a string into a table of integers by splitting with a given delimiter
Usage remarks / example:
---------------------------------------------------*/
BEGIN TRY DROP FUNCTION [dbo].[tfMain_StrToTblInt] END TRY BEGIN CATCH END CATCH
GO
CREATE FUNCTION [dbo].[tfMain_StrToTblInt](@InputStr nvarchar(MAX), @Delimiter nchar(1)) RETURNS @OutputTable TABLE ( Val int ) AS
BEGIN
DECLARE @TmpVal nvarchar(max);
WHILE LEN(@InputStr) > 0
BEGIN
SET @TmpVal = LTRIM(RTRIM(LEFT(@InputStr, ISNULL(NULLIF(CHARINDEX(@Delimiter, @InputStr) - 1, -1), LEN(@InputStr)))));
SET @InputStr = SUBSTRING(@InputStr, ISNULL(NULLIF(CHARINDEX(@Delimiter, @InputStr), 0), LEN(@InputStr)) + 1, LEN(@InputStr));
IF LEN(@TmpVal)>0 AND ISNUMERIC(@TmpVal)<>0
BEGIN
INSERT INTO @OutputTable ( Val ) VALUES ( CONVERT(int,@TmpVal) );
END
END
RETURN;
END
GO
BEGIN TRY DROP FUNCTION [dbo].[tfMain_StrToTblInt_Ordered] END TRY BEGIN CATCH END CATCH
GO
CREATE FUNCTION [dbo].[tfMain_StrToTblInt_Ordered](@InputStr nvarchar(MAX), @Delimiter nchar(1)) RETURNS @OutputTable TABLE ( RowNo int PRIMARY KEY, Val int ) AS
BEGIN
DECLARE @TmpVal nvarchar(max), @RowNo int; SET @RowNo=1;
WHILE LEN(@InputStr) > 0
BEGIN
SET @TmpVal = LTRIM(RTRIM(LEFT(@InputStr, ISNULL(NULLIF(CHARINDEX(@Delimiter, @InputStr) - 1, -1), LEN(@InputStr)))));
SET @InputStr = SUBSTRING(@InputStr, ISNULL(NULLIF(CHARINDEX(@Delimiter, @InputStr), 0), LEN(@InputStr)) + 1, LEN(@InputStr));
IF LEN(@TmpVal)>0 AND ISNUMERIC(@TmpVal)<>0
BEGIN
INSERT INTO @OutputTable ( RowNo, Val ) VALUES ( @RowNo, CONVERT(int,@TmpVal) );
IF @@ROWCOUNT>0 SET @RowNo=@RowNo+1;
END
END
RETURN;
END
GO
-------For Example --select val from dbo.[tfMain_StrToTblInt_Ordered]('7•4•5','•')
2. 分割成字符表
/* ---------------------------------------------------
tfMain_StrToTblStr (SQL_TABLE_VALUED_FUNCTION)
Description: Function to convert a string into a table of strings by splitting with a given delimiter
Usage remarks / example:
---------------------------------------------------*/
BEGIN TRY DROP FUNCTION [dbo].[tfMain_StrToTblStr] END TRY BEGIN CATCH END CATCH
GO
CREATE FUNCTION [dbo].[tfMain_StrToTblStr](@InputStr nvarchar(MAX), @Delimiter nchar(1)) RETURNS @OutputTable TABLE ( Val nvarchar(MAX) ) AS
BEGIN
DECLARE @TmpVal nvarchar(max);
WHILE LEN(@InputStr) > 0
BEGIN
SET @TmpVal = LEFT(@InputStr, ISNULL(NULLIF(CHARINDEX(@Delimiter, @InputStr) - 1, -1), LEN(@InputStr)));
SET @InputStr = SUBSTRING(@InputStr, ISNULL(NULLIF(CHARINDEX(@Delimiter, @InputStr), 0), LEN(@InputStr)) + 1, LEN(@InputStr));
INSERT INTO @OutputTable ( Val ) VALUES ( @TmpVal );
END
RETURN;
END
GO
BEGIN TRY DROP FUNCTION [dbo].[tfMain_StrToTblStr_Ordered] END TRY BEGIN CATCH END CATCH
GO
CREATE FUNCTION [dbo].[tfMain_StrToTblStr_Ordered](@InputStr nvarchar(MAX), @Delimiter nchar(1)) RETURNS @OutputTable TABLE ( RowNo int PRIMARY KEY, Val nvarchar(MAX) ) AS
BEGIN
DECLARE @TmpVal nvarchar(max), @RowNo int; SET @RowNo=1;
WHILE LEN(@InputStr) > 0
BEGIN
SET @TmpVal = LEFT(@InputStr, ISNULL(NULLIF(CHARINDEX(@Delimiter, @InputStr) - 1, -1), LEN(@InputStr)));
SET @InputStr = SUBSTRING(@InputStr, ISNULL(NULLIF(CHARINDEX(@Delimiter, @InputStr), 0), LEN(@InputStr)) + 1, LEN(@InputStr));
INSERT INTO @OutputTable ( RowNo, Val ) VALUES ( @RowNo, @TmpVal );
IF @@ROWCOUNT>0 SET @RowNo=@RowNo+1;
END
RETURN;
END
GO
-------For Example --select val from dbo.tfMain_StrToTblStr('1•4•5','•')
