Advertisement
Guest User

Delimited Text to GUID table SQL Table Function

a guest
Jan 29th, 2018
728
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2. /****** Object:  UserDefinedFunction [dbo].[fn_ParseText2GUIDTable]    Script Date: 1/29/2018 9:28:47 PM ******/
  3. SET ANSI_NULLS OFF
  4. GO
  5. SET QUOTED_IDENTIFIER OFF
  6. GO
  7. ALTER   function [dbo].[fn_ParseText2GUIDTable]
  8.  (
  9.  @p_SourceText  varchar(8000)
  10.  ,@p_Delimeter varchar(100) = ',' --default to comma delimited.
  11.  )
  12. RETURNS @retTable TABLE
  13.  (
  14.   Position  int identity(1,1)
  15.  ,guid_value UniqueIdentifier
  16.  )
  17. AS
  18. /*
  19. ********************************************************************************
  20. Purpose: Parse values from a delimited string
  21.   & return the result as an indexed table
  22. Copyright 1996, 1997, 2000, 2003 Clayton Groom (<A href="mailto:Clayton_Groom@hotmail.com">Clayton_Groom@hotmail.com</A>)
  23.  
  24. Modified for GUIDs by Trevor Fayas (tfayas@gmail.com - devtrev.com)
  25.  
  26. Posted to the public domain Aug, 2004
  27. 06-17-03 Rewritten as SQL 2000 function.
  28.  Reworked to allow for delimiters > 1 character in length
  29.  and to convert Text values to numbers
  30. ********************************************************************************
  31. */
  32. BEGIN
  33.  DECLARE @w_Continue  int
  34.   ,@w_StartPos  int
  35.   ,@w_Length  int
  36.   ,@w_Delimeter_pos int
  37.   ,@w_tmp_int  int
  38.   ,@w_tmp_num  numeric(18,3)
  39.   ,@w_tmp_txt   varchar(2000)
  40.   ,@w_tmp_guid uniqueidentifier
  41.   ,@w_Delimeter_Len tinyint
  42.  if len(@p_SourceText) = 0
  43.  begin
  44.   SET  @w_Continue = 0 -- force early exit
  45.  end
  46.  else
  47.  begin
  48.  -- parse the original @p_SourceText array into a temp table
  49.   SET  @w_Continue = 1
  50.   SET @w_StartPos = 1
  51.   SET @p_SourceText = RTRIM( LTRIM( @p_SourceText))
  52.   SET @w_Length   = DATALENGTH( RTRIM( LTRIM( @p_SourceText)))
  53.   SET @w_Delimeter_Len = len(@p_Delimeter)
  54.  end
  55.  WHILE @w_Continue = 1
  56.  BEGIN
  57.   SET @w_Delimeter_pos = CHARINDEX( @p_Delimeter
  58.       ,(SUBSTRING( @p_SourceText, @w_StartPos
  59.       ,((@w_Length - @w_StartPos) + @w_Delimeter_Len)))
  60.       )
  61.  
  62.   IF @w_Delimeter_pos > 0  -- delimeter(s) found, get the value
  63.   BEGIN
  64.    SET @w_tmp_txt = LTRIM(RTRIM( SUBSTRING( @p_SourceText, @w_StartPos
  65.         ,(@w_Delimeter_pos - 1)) ))
  66.     set @w_tmp_guid = cast(@w_tmp_txt as uniqueidentifier)
  67.    SET @w_StartPos = @w_Delimeter_pos + @w_StartPos + (@w_Delimeter_Len- 1)
  68.   END
  69.   ELSE -- No more delimeters, get last value
  70.   BEGIN
  71.    SET @w_tmp_txt = LTRIM(RTRIM( SUBSTRING( @p_SourceText, @w_StartPos
  72.       ,((@w_Length - @w_StartPos) + @w_Delimeter_Len)) ))
  73.     set @w_tmp_guid = cast(@w_tmp_txt as uniqueidentifier)
  74.    SELECT @w_Continue = 0
  75.   END
  76.   INSERT INTO @retTable VALUES( @w_tmp_guid )
  77.  END
  78. RETURN
  79. END
Advertisement
RAW Paste Data Copied
Advertisement