SPLIT STRING IN SQL SERVER TABLULAR VALUE EXAMPLE

ALTER

FUNCTION [dbo].[FN_SPLIT_STRING]

(@StrParam varchar(300), @Delimeter char(1))

Returns

@SplitedString Table

(

StrSplit Varchar(50) )

AS

BEGIN

DECLARE @StrSplit Varchar(50), @NextDelimiter Int

WHILE(LEN(@StrParam) > 0)

BEGIN

Select @NextDelimiter = CharIndex(@Delimeter, @StrParam)

IF(@NextDelimiter > 0)

Begin

Set @StrSplit = Left(@StrParam, @NextDelimiter - 1)

Set @StrParam = Right(@StrParam, Len(@StrParam) - @NextDelimiter)

End

Else

Begin

Set @StrSplit = @StrParam

Set @StrParam = ''

End

Insert Into @SplitedString Values(@StrSplit)

END

Return

End

 

EXAMPLE    
    
SELECT * FROM [FN_SPLIT_STRING]('SHANEESH','S')    
1    
2 HANEE   
3 H   
    
SELECT * FROM [FN_SPLIT_STRING]('SHANEESH PATEL','S')    
1    
2 HANEE   
3 H PATEL   
    
    
SELECT * FROM [FN_SPLIT_STRING]('SHANEESH PATEL','H')    
1 S
2 ANEES
3  PATEL
 
SELECT * FROM [FN_SPLIT_STRING]('SHANEESH PATEL','T') 
1 SHANEESH PA
2 EL