daily pastebin goal
61%
SHARE
TWEET

Delimited Text to GUID table SQL Table Function

a guest Jan 29th, 2018 416 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
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top