Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Tagged as

Singularize Function in TSQL

, 13 Sep 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
Singularize Function in TSQL

Today I was trying to make a code generator for one of our projects to make our lives easier. Basically I was trying to list all objects on our SQL Server database and print a C# code output which I can then save as a .cs file. All went fine until I realized that Entities must be singularized, also we have a standard naming convention in our database where every table is pluralized. So I started searching online whether there is a free TSQL code to Singularize strings and got no results so I resorted to making one of my own.

To my surprise, it was not that hard (unless I missed something) specially with the ever trusted Wikipedia I searched for what are the different plural formats out there and that is where I based my codes on. So if you need them, please feel free to use the code below and even improve it if you wanted to just share back any improvements you have made.

CREATE FUNCTION Singularize
(
	@FieldName varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
	DECLARE @Output varchar(max)

	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 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Raymund Macaalay
Technical Lead
New Zealand New Zealand
http://nz.linkedin.com/in/macaalay
http://macaalay.com/
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141223.1 | Last Updated 13 Sep 2011
Article Copyright 2011 by Raymund Macaalay
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid