tpcolson

What's the Word? Enforcing Naming Standards

Blog Post created by tpcolson on Jan 19, 2015

Do you have someone in your organization that YELLS WITH THEIR KEYBOARD? Everything is upper case? What about the rogue all-lower-case folks? Or ever worse, the First-word-is-proper-case-all-other-words-are-lower-case ninjas.

 

I have a personal pet-peeve (OCD). Feature names in GIS should be Proper Case. Happy Valley Road. Not Happy valley road, not Happy valley Road, and definitely not HAPPY VALLEY ROAD.

 

I'd like to enforce Proper Case naming of those feature regardless of how the text is cased by the editor. This can be accomplished with a simple Function and Trigger in SQL.

 

First create the following Function:

 

create function [dbo].[ProperCase](@Text as varchar(8000))
returns varchar(8000)
as
begin
   declare @Reset bit;
   declare @Ret varchar(8000);
   declare @i int;
   declare @c char(1);


   select @Reset = 1, @i=1, @Ret = '';
   
   while (@i <= len(@Text))
    select @c= substring(@Text,@i,1),
               @Ret = @Ret + case when @Reset=1 then UPPER(@c) else LOWER(@c) end,
               @Reset = case when @c like '[a-zA-Z]' then 0 else 1 end,
               @i = @i +1
   return @Ret
end
GO

 

Then this Triggger:

 

CREATE TRIGGER [dbo].[NAME_UPDATE]
ON [dbo].[SOME_TABLE]
AFTER INSERT NOT FOR REPLICATION
AS BEGIN
SET NOCOUNT ON; 
UPDATE SOME_TABLE
SET
NAME = dbo.Propercase(NAME)
END
GO

 

Note here that this only fires after an insert, not an update. There could be legitimate reason why the YELLERS want something other than proper case, e.g. "ND Happy Valley Road" (ND for "North District). This allows them (or me after they YELL at me) to update that one feature without the trigger proper-casing my edit.

 

This is a personal blog and does not recommend, endorse, or support the methods described above. Alteration of data using SQL outside of the ESRI software stack, of course, is not supported and should not be applied to a production database without a thorough understanding and disaster recovery plan.

Outcomes