SQL Scripts for Microsoft Dynamics GP: Table Function to Split String on Delimiter

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script creates a table function which will split a string based on the supplied delimiter; it was created for a user who split their item number into segments, separated with hyphens:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
IF OBJECT_ID('[dbo].[uf_AZRCRV_SplitString]') IS NOT NULL

BEGIN

	DROP FUNCTION [dbo].[uf_AZRCRV_SplitString];

END;

GO
CREATE FUNCTION [dbo].[uf_AZRCRV_SplitString](

    @String VARCHAR(8000),

    @Delimiter VARCHAR(10)

)

RETURNS @ReturnTable TABLE(

    id int IDENTITY(1,1) NOT NULL,

    part varchar(50) NULL

)

AS

BEGIN

	Declare @iSpaces int

	Declare @part varchar(50)
	Set @String = RTRIM(@String)
	--initialize spaces

	Select @iSpaces = charindex(@Delimiter, @String, 0)

	While @iSpaces > 0
	Begin

		Select @part = substring(@String, 0, charindex(@Delimiter, @String, 0))
		Insert Into @ReturnTable(part)

			Select @part
		Select @String = substring(@String,charindex(@Delimiter, @String, 0) + len(@Delimiter),len(@String) - charindex(' ', @String, 0))
		Select @iSpaces = charindex(@Delimiter, @String, 0)

	end
	If len(@String) > 0

		Insert Into @ReturnTable

			Select @String
    RETURN

END

GO

Read original post SQL Scripts for Microsoft Dynamics GP: Table Function to Split String on Delimiter at azurecurve|Ramblings of an IT Professional