Sql function for creating a plural noun

Question

Is there any function in SQL Server that modifies a noun from singular to plural?

Answer 1

This feature does not exist in SQL Server.

Answer 2

SQL itself has nothing of the kind, but you can try using the .NET PluralizationService introduced in .NET 4 - the same functionality as the Entity Framework for pluralizing / uniqueness of table names for object names.

You will need to write an SQL-CLR assembly to access pluralization services, but it definitely seems doable!

Answer 3

The function does not exist in SQL Server, as mentioned in @aF. One place I know if exists in Entity Framework 4+. A pluralization object can be created and used .

SQL has the ability to run CLR code - through SQL CLR. The main problem is that the SQL CLR is limited to the .NET Framework 3.5. Thus, you will need to write .net 4 code that will work on your tables. Alternatively, you can use a product like Reflector and rebuild a compatible version of 3.5 and run it in SQL Server.

Answer 4

CREATE FUNCTION dbo.Pluralize ( @noun nvarchar(50) ) RETURNS nvarchar(50) AS BEGIN DECLARE @QueryString nvarchar(4000) SET @QueryString = N'FORMSOF(INFLECTIONAL,"' + @noun + N'")' RETURN (SELECT TOP 1 display_term FROM sys.dm_fts_parser(@QueryString,1033,0,0)) END GO SELECT noun, dbo.Pluralize(noun) FROM (VALUES('cat'), ('mouse'), ('goose'), ('person'), ('man'), ('datum')) nouns(noun) 

Returns

 noun ------ ------------------------------ cat cats mouse mice goose geese person persons man men datum data 

Unfortunately, he simply relies on the observation that the term extension TOP 1 for the noun is a plural form. I doubt this is documented anywhere.

Answer 5

  DECLARE @PluralVersion nvarchar(128) = '' DECLARE @TableName nvarchar(128) = 'MyTableName' DECLARE @NounVersions TABLE(Term nvarchar(128) NOT NULL) DECLARE @QueryString nvarchar(4000) SET @QueryString = N'FORMSOF(INFLECTIONAL,"' + @TableName + N'")' INSERT INTO @NounVersions SELECT TOP 10 display_term FROM sys.dm_fts_parser(@QueryString,1033,0,0) SELECT TOP 1 @PluralVersion = Term FROM @NounVersions WHERE Term Not Like '%''%' AND RIGHT(Term,1) = 's' SET @PluralVersion = UPPER(LEFT(@PluralVersion,1))+LOWER(SUBSTRING(@PluralVersion,2,LEN(@PluralVersion))) SELECT @PluralVersion 

I know this is an old thread, but I thought that I would publish it anyway. Based on what Martin started, I expanded it, and it still does the OK job. I would not say that it is wonderful. This is used for multiple table names, so it is very controlled.

Answer 6

No, but it would be pretty easy to make a table for this if you have a limited set of words to check.

Example:

 CREATE TABLE dbo.Plurals ( id int IDENTITY, singular varchar(100), plural varchar(100) ) INSERT INTO dbo.Plurals VALUES ('cat', 'cats'), ('goose', 'geese'), ('man', 'men'), ('question', 'questions') 

Alternatively, you can make the table just exceptions, that is, words that cannot be pluralized by simply adding s , then you could do an EXISTS check on that table if it wasn’t there, add s , and if it then looks at the plural .

Answer 7

Do you want to use this for display?
Something like "Your search returned 1 results " / "Your search returned 4 results "?

If so, I would not do that.
Finding or writing a function that does this correctly for all special cases (not to mention several languages) is almost impossible, and storing each required text once in the singular and once in the plural is not much better.

At work, I deal with several languages ​​and many dynamically generated sentences like this, and I found that completely eliminating differences in single / multiple forms is generally the easiest management solution:

"Number of results for this search: 1"