×

Welcome to TagMyCode

Please login or create account to add a snippet.
0
0
 
0
Language: SQL
Posted by: Arturo Bardelli
Added: Sep 22, 2016 9:49 AM
Views: 15
  1. USE [PWM_r3]
  2. GO
  3. /****** Object:  UserDefinedFunction [dbo].[Split]    Script Date: 09/22/2016 11:46:24 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. ALTER FUNCTION [dbo].[Split](
  9.        @VALUE NVARCHAR (4000),
  10.        @delimiter NVARCHAR (10)
  11. )
  12. RETURNS @retTable TABLE ([VALUE] NVARCHAR(4000))
  13. BEGIN
  14.       IF @VALUE IS NULL
  15.       BEGIN
  16.             INSERT INTO @retTable ([VALUE]) VALUES (NULL)
  17.             RETURN
  18.       END
  19.      
  20.       --Initialize
  21.       DECLARE @NextString NVARCHAR(4000)
  22.       DECLARE @Pos INT
  23.       SET @NextString = ''
  24.       SET @VALUE = @VALUE + @delimiter
  25.      
  26.       --Get position of first Comma
  27.       SET @Pos = charindex(@delimiter,@VALUE)
  28.      
  29.       WHILE (@Pos <>  0)  
  30.       BEGIN
  31.             SET @NextString = SUBSTRING(@VALUE,1,@Pos - 1)
  32.             INSERT INTO @retTable ([VALUE]) VALUES (@NextString)
  33.             SET @VALUE = SUBSTRING(@VALUE,@Pos +1,len(@VALUE))
  34.             SET @Pos  = charindex(@delimiter,@VALUE)
  35.       END
  36.       RETURN
  37. END
  38.