function output

• Shree23

  SSC-Addicted

  Points: 497

  More actions

  February 5, 2021 at 5:08 am

  #3841302

  Hi Expert,

   

  I am troubleshooting this function and unable to understand the actual output from it . could you please explain it

   

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

   

  ALTER FUNCTION

  Create funciton dbo.test1

  (

  @List nvarchar(2000),

  @SplitOn nvarchar(5)

  )

  RETURNS @RtnValue table

  (

   

  Id int identity(1,1),

  Value nvarchar(100)

  )

  AS

  BEGIN

   

  While (Charindex(@SplitOn,@List)>0)

  Begin

   

  Insert Into @RtnValue (value)

  Select

  Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))

  Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))

  End

   

  Insert Into @RtnValue (Value)

  Select Value = ltrim(rtrim(@List))

  Return

  END

   

   

  Shree

   

   

• Ken McKelvey

  SSCoach

  Points: 18902

  More actions

  February 5, 2021 at 9:48 am

  #3841438

  This is a bad string splitting table valued function.

  Use the following instead:

  https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%e2%80%9ccsv-splitter%e2%80%9d-function