-- DROP IF EXISTS FUNCION dbo.utils_Singularize
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[utils_Singularize]')
	AND type in (N'FN', N'IF',N'TF', N'FS', N'FT'))
BEGIN
	DROP FUNCTION [dbo].[utils_Singularize]
END
GO

-- =============================================
-- Author:		Pablo Lopez<juan.garcia@toptal.com>
-- Create date: 2019-06-06
-- Description:	Singularizes a word
--
--	example: utils_Singularize('Quotes') returns 'Quote'
-- =============================================
Create FUNCTION [dbo].[utils_Singularize]
(
	@FieldName varchar(128)
)
RETURNS varchar(128)
AS
BEGIN
	DECLARE @Output varchar(128)

	IF @FieldName NOT LIKE '%s'
	-- already singular
	BEGIN
		SET @Output = @FieldName
	END

	ELSE IF @FieldName LIKE '%ss'
	-- already singular ie. mass, chess
	BEGIN
		SET @Output = @FieldName
	END

	ELSE IF @FieldName LIKE '%ies' 
	-- ie. cherries, ladies
	BEGIN
		SET @Output = SUBSTRING(@FieldName, 1, LEN(@FieldName)-3) + 'y'
	END

	ELSE IF @FieldName LIKE '%oes' 
	-- ie. heroes, potatoes
	BEGIN
		SET @Output = SUBSTRING(@FieldName, 1, LEN(@FieldName) -2)
	END

	ELSE IF @FieldName LIKE '%es' and SUBSTRING(@FieldName, LEN(@FieldName)-2, 1) in ('a', 'e', 'i', 'o', 'u')
	-- ie. massages, phases
	BEGIN
		SET @Output = SUBSTRING(@FieldName, 1, LEN(@FieldName) -1)
	END

	ELSE IF @FieldName LIKE '%es' and SUBSTRING(@FieldName, LEN(@FieldName) -2, 1) in ('h')
	-- ie. witches, dishes
	BEGIN
		SET @Output = SUBSTRING(@FieldName, 1, LEN(@FieldName) - 2)
	END

	ELSE IF @FieldName LIKE '%es' and SUBSTRING(@FieldName, LEN(@FieldName) -2, 1) in ('b','c','d','f','g','j','k','l','m','n','p','q','r','s','t','v','w','x','y','z')
	-- ie. kisses, judges
	BEGIN
		SET @Output = SUBSTRING(@FieldName, 1, LEN(@FieldName) - 1)
	END

	ELSE IF @FieldName LIKE '%s'
	-- ie. laps, clocks, boys
	BEGIN
		SET @Output = SUBSTRING(@FieldName, 1, LEN(@FieldName) -1)
	END

	RETURN @Output
END
GO


