/*********************************************************************************************************************
Purpose : Get the split list as Table
Returns : Splitted string Table
Arguments : select * from fnTableSplitString('ab~cde~~efgh~~~','~',1)
=====================================================================================================================
Created By : Suresh Kumar N
Created On : 16-Mar-2011
----------------------------------------------------------------------------------------------------------------------
Modification Log
----------------------------------------------------------------------------------------------------------------------
Modified By Modified Date Reason
----------------------------------------------------------------------------------------------------------------------
************************************************************************************************************************/ create FUNCTION [dbo].[fnTableSplitString](@InputString nvarchar(max),@Separator char(1),@IsUserCall bit = 1)RETURNS @Return TABLE (SplitList varchar(8000))AS
BEGINIF(@IsUserCall = 1)BEGININSERT @Return SELECT * FROM fnTableSplitString((@InputString + '' + @Separator + ''),@Separator,0)ENDELSEBEGIN--Declare the CTE for getting the indexes of each word--Getting the star and end index of each wordWITH cteTableValueCollection(StartIndex,EndIndex) AS(
SELECT CAST(0 AS BIGINT) as StartIndex,CHARINDEX(@Separator,@InputString) EndIndexUNION ALLSELECT EndIndex+1,CHARINDEX(@Separator,@InputString,EndIndex+1)
FROM cteTableValueCollectionWHERE EndIndex > 0)
-- Select the splitted string without empty string
DBO
cteTableValueCollection
EndIndex
INSERT @Return SELECT .fnTrim(SUBSTRING(@InputString,StartIndex,EndIndex-StartIndex)) SplitListFROM WHERE > 0 And (EndIndex-startIndex) > 0END
ENDRETURN
Purpose : Get the split list as Table
Returns : Splitted string Table
Arguments : select * from fnTableSplitString('ab~cde~~efgh~~~','~',1)
=====================================================================================================================
Created By : Suresh Kumar N
Created On : 16-Mar-2011
----------------------------------------------------------------------------------------------------------------------
Modification Log
----------------------------------------------------------------------------------------------------------------------
Modified By Modified Date Reason
----------------------------------------------------------------------------------------------------------------------
************************************************************************************************************************/ create FUNCTION [dbo].[fnTableSplitString](@InputString nvarchar(max),@Separator char(1),@IsUserCall bit = 1)RETURNS @Return TABLE (SplitList varchar(8000))AS
BEGINIF(@IsUserCall = 1)BEGININSERT @Return SELECT * FROM fnTableSplitString((@InputString + '' + @Separator + ''),@Separator,0)ENDELSEBEGIN--Declare the CTE for getting the indexes of each word--Getting the star and end index of each wordWITH cteTableValueCollection(StartIndex,EndIndex) AS(
SELECT CAST(0 AS BIGINT) as StartIndex,CHARINDEX(@Separator,@InputString) EndIndexUNION ALLSELECT EndIndex+1,CHARINDEX(@Separator,@InputString,EndIndex+1)
FROM cteTableValueCollectionWHERE EndIndex > 0)
-- Select the splitted string without empty string
DBO
cteTableValueCollection
EndIndex
INSERT @Return SELECT .fnTrim(SUBSTRING(@InputString,StartIndex,EndIndex-StartIndex)) SplitListFROM WHERE > 0 And (EndIndex-startIndex) > 0END
ENDRETURN
06:23 |
Category:
SQL SERVER
|
0
comments
Comments (0)