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
Pingback: Simple TSQL and C# Entity Generator for S#arp Architecture « Raymund Macaalay's Dev Blog
Man – men, woman – women. 🙂
Right! Thanks for noting that, will try to put a rule on it