ALL CAPS in Addresses

M. Jane Orosco

If you use the standard NCOA process provided by the Tessitura Network with zeta global as the data provider.  You can request a mixed case set of cleaned up values.  Check with Mark Mellynchuk <mmellynchuk@zetaglobal.com> over at Zeta for details.

--Tom

Thank you for the info.  We use TRG so I asked if this is something they can do on our next update.

Appreciate you!

You can bulk update all or a selection Street 1 addresses or first names in the database if you have a bit of SQL  I have a little scalar function in my database called FS_AllToProperCase.  I wrote a script that runs uses that function and (for example) will look for Street1 fields that are all lower case and compare them to what they will change too.  When I'm happy with the results I commit the changes.  I can do the same with City (or anything really).

It's great if you want to do a bit of database cleaning. 

You can bulk update all or a selection Street 1 addresses or first names in the database if you have a bit of SQL  I have a little scalar function in my database called FS_AllToProperCase.  I wrote a script that runs uses that function and (for example) will look for Street1 fields that are all lower case and compare them to what they will change too.  When I'm happy with the results I commit the changes.  I can do the same with City (or anything really).

It's great if you want to do a bit of database cleaning. 

Heath Wilder -- would you be willing to share your code for that function?

Yeah sure 

USE [impresario]
GO

/****** Object:  UserDefinedFunction [dbo].[FS_AllToProperCase]    Script Date: 5/05/2022 10:49:59 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[FS_AllToProperCase]( @name nvarchar(500) )
RETURNS nvarchar(500)
AS
BEGIN
declare @pos    int = 1
      , @pos2   int

if (@name <> '')--or @name = lower(@name) collate SQL_Latin1_General_CP1_CS_AS or @name = upper(@name) collate SQL_Latin1_General_CP1_CS_AS)
begin
    set @name = lower(rtrim(@name))
    while (1 = 1)
    begin
        set @name = stuff(@name, @pos, 1, upper(substring(@name, @pos, 1)))
        set @pos2 = patindex('%[- ''.)(]%', substring(@name, @pos, 500))
        set @pos += @pos2
        if (isnull(@pos2, 0) = 0 or @pos > len(@name))
            break
    end
end

return @name
END
GO

There are probably cooler ways of doing it but if I was going to Proper all the Lower case "Street 1" 

Begin transaction 

declare  @Proper table 
	(
	Word varchar(4000),
	ID int
	)

insert into @Proper (Word, ID)
	select dbo.FS_AllToProperCase(street1) as test,
	address_no
from T_ADDRESS
where street1 = LOWER(street1) collate SQL_Latin1_General_CP1_CS_AS
and inactive='N'

select * from @Proper

select address_no,
p.id,
c.street1,
p.Word

from T_ADDRESS c
Join @Proper p
on address_no = p.ID


--update T_ADDRESS 
--SET street1 = p.word
--from T_ADDRESS c
--Join @Proper p on c.address_no = p.ID
--where street1 = LOWER(street1) collate SQL_Latin1_General_CP1_CS_AS

Rollback transaction

Heath this is quite amazing and I hope to utilize it in the near future. I have to imagine that you run into issues for instance in names like McMullin etc. Is that when you would manually opt it out of your procedure or function?

Yep definitely.  That comparison table is helpful for that and other things you might not expect.  I often will play with exclusions in the "where" statement to exclude certain stuff depending on the field.