UPPER/lower case customer name: How To Change Names In Bulk

UPPER/lower case customer name: How To Change Names In Bulk

The steps are:

1. create a temp tbl of the existing and new names, create a db function lfn_capitalize_1st_letter_rest_lower;
2. create a Tessitura list of customers to be excluded from the name change, for instance Donors;
3. populate the temp tbl of the existing and new names;
4. present the result to users to get approval;
5. create a Tessitura list of customers with names to be changed;
6. run a script to change names;
7. run a script to change salutations.

------------
1. create a temp tbl of the existing and new names, create a db function lfn_capitalize_1st_letter_rest_lower
------------
CREATE TABLE TMP_CHC_NAME_CHANGE(
 [customer_no] [int] NOT NULL  primary Key,
 [L_name_existing] [varchar](55) ,
 [L_name_new] [varchar](55) ,
 [F_name_existing] [varchar](20) ,
 [F_name_new] [varchar](55) ,
 [M_name_existing] [varchar](20) ,
 [M_name_new] [varchar](55)
)
grant select on TMP_CHC_NAME_CHANGE to impusers

create function lfn_capitalize_1st_letter_rest_lower (
@p_str   varchar(8000)
) returns  varchar(8000)
AS
--it capitalize first letter of a word
--if it is a multi-part word with a separator as '.', ',', '-', ' ' then it capitalizes all parts of the word
--for instance
--select dbo.lfn_capitalize_1st_letter_rest_lower ( 'a.  bbb - 12   cde,JR.' )
--result: A. Bbb - 12 Cde,Jr.

begin
declare
@l_str   varchar(8000),
@l_str_len   int,
@l_curr_posit  int,
@l_letter_curr  char(1),
@l_str_out   varchar(8000),
@l_was_Separator_YN char(1)

set @l_str=lower(ltrim(rtrim(@p_str)))
set @l_str=REPLACE(@l_str,'  ',' ') --removing extra spaces
set @l_str=REPLACE(@l_str,'  ',' ')
set @l_str=REPLACE(@l_str,'  ',' ')

--making 1st letter capital:
set @l_str=upper( substring(@l_str,1,1) ) + substring(@l_str,2,8000)

--for a multi-part word making 1st letter capital for each part:
set @l_str_len=  len(@l_str)
set @l_curr_posit= 1
set @l_str_out= ''
set @l_was_Separator_YN='N' --if a current letter is a separator:  '.', ',', '-', ' '

while @l_curr_posit <= @l_str_len
begin
  set @l_letter_curr=substring(@l_str, @l_curr_posit, 1)

if @l_was_Separator_YN='N'
  set @l_str_out=@l_str_out+@l_letter_curr
else
  set @l_str_out=@l_str_out+UPPER(@l_letter_curr)

if @l_letter_curr in ('.', ',', '-', ' ') --HARDCODED!
  set @l_was_Separator_YN='Y'
else
  set @l_was_Separator_YN='N'

set @l_curr_posit=@l_curr_posit+1
end --while

return @l_str_out
end

------------
2. create a Tessitura list of customers to be excluded from the name change, for instance Donors
------------
--double-check the list contents:
T_LIST_contents where list_no=<customers to be excluded>

------------
3. populate the temp tbl of the existing and new names
------------
--please check out the restrictions utilized in this SQL

--START
insert into TMP_CHC_NAME_CHANGE(
customer_no,
L_name_existing,
L_name_new,
F_name_existing,
F_name_new,
M_name_existing,
M_name_new
) SELECT
c.customer_no,
L_name_existing=ltrim(rtrim(lname)),
L_name_new =dbo.lfn_capitalize_1st_letter_rest_lower(ltrim(rtrim(lname))) ,
F_name_existing=ltrim(rtrim(fname)),
F_name_new =dbo.lfn_capitalize_1st_letter_rest_lower(ltrim(rtrim(fname))) ,
M_name_existing=ltrim(rtrim( isnull(mname,'') )),
M_name_new =dbo.lfn_capitalize_1st_letter_rest_lower(ltrim(rtrim( isnull(mname,'') )))  
from t_customer c
where 1=1
and c.inactive=1------------->>
and cust_type=1 --Individual  ------------->>
--------------SELECT ONLY LOWERCASE AND UPPERCASE LAST NAMES' CUSTOMERS:--------------
and (   CAST( lower(ltrim(rtrim(lname))) AS varbinary(100)) = CAST(ltrim(rtrim(lname)) AS varbinary(100)) 
    or  CAST( UPPER(ltrim(rtrim(lname))) AS varbinary(100)) = CAST(ltrim(rtrim(lname)) AS varbinary(100)) )
and  not exists (select 1 from T_LIST_contents L where  L.customer_no=c.customer_no and list_no=<customers to be excluded> ) ------------->>
and  CAST( substring(ltrim(rtrim(lname)),1,2) AS varbinary(100))<> 0x5053 --HARDCODED! 0x5053=PS [Public School]------------>>
--to exclude those which are not changing:
and CAST(dbo.lfn_capitalize_1st_letter_rest_lower(ltrim(rtrim(lname))) AS varbinary(100))<>CAST(ltrim(rtrim(lname)) AS varbinary(100))
--FINISH

------------
4. present the names to Tessitura users to get approval  
------------
--Dump the result to an Excel file:
select*from TMP_CHC_NAME_CHANGE
order by
CAST( ltrim(rtrim(L_name_existing)) AS varbinary(100) ),
CAST( ltrim(rtrim(F_name_existing)) AS varbinary(100) )

------------
5. create a Tessitura list of customers with names to be changed
------------
-- list #xx IT_sb_NAME_CHANGE_case:
select c.customer_no
from TMP_CHC_NAME_CHANGE sb, t_customer c
where c.customer_no =sb.customer_no

--double-check the list contents:
T_LIST_contents where list_no=<xx>

------------
6. run the script to change names
------------
update c set 
fname=F_name_new,
mname=M_name_new,
lname=L_name_new
from TMP_CHC_NAME_CHANGE sb, t_customer c
where c.customer_no =sb.customer_no

------------
7. run the script to change salutations
------------
Please verify first the existing salutation types:
select*from tr_signor
---expected result:
ID description
0 Default

--check out how many customers have Default salutations, the number must be close to total number of customers:
select count(*) from TX_CUST_SAL where signor=0 -- = Default 
select count(*) from t_customer

--this script changes only one salutation type at a time; if multiple types need to be changed run it as needed
exec up_rebuild_salutations
@list_no = <xx>, -- IT_sb_NAME_CHANGE_case
@salutation_type = 0,-------------------------double-check that 0  is for Default! ------------------------->>
@update_sort_name = 'Y',
@update_group_name = 'Y' 

 
 Thanks,
 Simon Basyuk,
 DBA of Carnegie Hall
 sbasyuk@carnegiehall.org