12/16/2013

Split values based on deleimiter in SQL Server


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

Tableau interview questions and answers for experienced professionals

  Tableau Interview Questions and Answers for experienced professional 1. What is TABLEAU? Tableau  is the powerful and fastest visualizing ...