Day: April 27, 2023

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