Custom SQL Server Function to Get By Delimiter (and index)

Had to look for over 30 minutes to figure this one out.  A simple split function in SQL does not exist. Almost all solutions point you to a table as result.  But I was looking for a simple function that would allow me to split a string by delimiter and let me pick a certain index.

Something like :

getByDelimiter('hi|ho|hey','|',1) => "hi"
getByDelimiter('hi|ho|hey','|',2) => "ho"
getByDelimiter('hi|ho|hey','|',3) => "hey"

I finally found a solution on the web (but had to bugfix it)




Here is the code :

/****** Object:  UserDefinedFunction [dbo].[fnGetByDelimiter]    Script Date: 14/04/2017 20:06:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fnGetByDelimiter]
(
  @multiwordstring VARCHAR(255),
  @delimiter VARCHAR(1),
  @wordnumber      NUMERIC
)
returns VARCHAR(255)
AS
  BEGIN
      DECLARE @remainingstring VARCHAR(1024)
      SET @remainingstring=@multiwordstring

      DECLARE @numberofwords NUMERIC
      SET @numberofwords=(LEN(@remainingstring) - LEN(REPLACE(@remainingstring, @delimiter, '')) + 1)

      DECLARE @word VARCHAR(50)
      DECLARE @parsedwords TABLE
      (
         line NUMERIC IDENTITY(1, 1),
         word VARCHAR(255)
      )

      WHILE @numberofwords > 1
        BEGIN
            SET @word=LEFT(@remainingstring, CHARINDEX(@delimiter, @remainingstring) - 1)

            INSERT INTO @parsedwords(word)
            SELECT @word

            SET @remainingstring= RIGHT(@remainingstring,LEN(@remainingstring)-LEN(Concat(@word, @delimiter)))
            SET @numberofwords=(LEN(@remainingstring) - LEN(REPLACE(@remainingstring, @delimiter, '')) + 1)

            IF @numberofwords = 1
              BREAK

            ELSE
              CONTINUE
        END

      IF @numberofwords = 1
        SELECT @word = @remainingstring
      INSERT INTO @parsedwords(word)
      SELECT @word

      RETURN
        (SELECT word
         FROM   @parsedwords
         WHERE  line = @wordnumber)

  END
GO