In Some scenarios we need to split a string
value based on the delimiter value so if we have a table which contains string
values in one column and we need to split them based on delimiter value or in
SSRS we have a parameter value which is in form of coming comma separated value
then we can designed a user defined function which will be accepting string value
with delimiter to split those given values.
So here I have designed a split function to
split all strings based on the delimiter value to return as a object as:
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[UDF_Split_String_To_Table] (
@StringVal VARCHAR(max), /* Variable to accept input string which need to split
absed on the delimiter*/
@Delimeter char(1), /* Variable to accept delimiter type*/
@TrimSpace bit ) /* To kill
whitespace? */
RETURNS @ResultTable TABLE ( [Value] VARCHAR(4000) )
AS
BEGIN
DECLARE @ResVal
VARCHAR(4000)
WHILE LEN(@StringVal) > 0
BEGIN
SET @ResVal = LEFT(@StringVal,
ISNULL(NULLIF(CHARINDEX(@Delimeter, @StringVal) - 1, -1),
LEN(@StringVal)))
SET @StringVal = SUBSTRING(@StringVal,
ISNULL(NULLIF(CHARINDEX(@Delimeter, @StringVal), 0),
LEN(@StringVal)) + 1, LEN(@StringVal))
IF @TrimSpace = 1 Set @ResVal = LTRIM(RTRIM(@ResVal))
INSERT INTO @ResultTable ( [Value] )
VALUES ( @ResVal )
END
RETURN
END
GO
If you want to split a string value, based
on the delimiter and want to show each value in a separate column than go
through this post:Split
function to show delimiter separated value in separate columns
