Author Topic: Capitalising Last and First and getting Sort name and Display Name to update (Read 3593 times)

petednz · July 07, 2010, 03:05:44 pm

Hi - we are looking at a civicrm db that has a lot of lower case first and last names. If I run something like

UPDATE `civicrm_contact` SET
`first_name` = CONCAT(UPPER(LEFT(`first_name`, 1)), LOWER(SUBSTRING(`first_name`, 2)))

then I get the 'john' to be 'John' but no impact on Sort or Display - so am missing bit of puzzle to run an update on the Sort/Display fields.

(I realise above probably needs to be more sophisticated to handle eg turning mary-ann to Mary-Ann and 'te ringa' to be 'Te Ringa' so any help in that direction appreciated too)

Thanks in advance

xavier · July 07, 2010, 10:01:41 pm

you also need to UPDATE sort_name and display_name columns.

As for automatically setting the uppercase in the name, you will never be able to have all the case working, they are so many edge cases.

For instance the rules about if you put a uppercase on the 'von' are different in Belgium or Netherland. (one of the erik would be able to get you the details), and last names in japan are all uppercases.

X+

petednz · July 08, 2010, 03:14:08 am

thanks - good point about the von etc

petednz · August 28, 2010, 11:15:03 pm

anyone able to offer the correct sql for updating Sort and Display names. I realise this needs to deal with NULL (eg First is NULL but Last is not and v.v.) so CONCAT won't do, but CONCAT_WS looks helpful, but this will still miss cases, eg both First and Last are NULL therefore Sort and Display should (presumably) show Primary Email if it is not NULL

But maybe there is an easier query that effectively is same as opening each Contact Record and Saving - since this is what does the job at the backend.

Anyone?

Donald Lobo · August 29, 2010, 06:27:33 am

might be easier to treat each case differently, i.e. run multiple queries for the combinations of non-null and null. might be easier and quicker that way

lobo

petednz · August 29, 2010, 02:20:19 pm

hi - thx - yes i went down that route

xavier · August 30, 2010, 12:06:27 am

Hi,

Could you share the queries you came up with at the end ?

X+

petednz · August 30, 2010, 12:23:06 am

yes if you promise not to laugh

jimmyjam · October 13, 2010, 12:48:29 pm

Hi Peter,

Would you mind sharing the SQL query you ran? I have a bunch of contacts that are erroneously showing their email address as the Sort Name, rather than the default format: {contact.last_name}{, }{contact.first_name}

I'm hoping a query to fix the problem.

Thanks,
James

petednz · October 13, 2010, 02:35:54 pm

james

With all usual caveats etc, here are a bunch of queries I have used at various times. Caution include fact that they may all not have the full set of 'check if things are NULL or of type XXX before doing this' as we were operating in some odd situations.

Maybe these are the sorts of tips that belong in the wiki somewhere, like a Library of sql queries for fixing common hiccups. Am sure there are others more proficient at these so corrections etc are all welcomed.

Update Display Name for Orgs

Code: [Select]

UPDATE `civicrm_contact` 
SET `display_name` = `organization_name` 
WHERE `contact_type`= "Organization"

Update Display name from First/Last

Code: [Select]

UPDATE `civicrm_contact` 
SET `display_name` = CONCAT(first_name,' ',last_name)
WHERE `contact_type`= "Individual"

Update Display Name if First/Last = NULL using Email

Code: [Select]

 UPDATE `civicrm_contact` 
 LEFT JOIN `civicrm_email` 
 ON `civicrm_contact`.`id`= `civicrm_email`.`contact_id` 
 SET `display_name`= `email` 
 WHERE `contact_type` LIKE 'Individual' 
 AND `first_name` IS NULL 
 AND `last_name` IS NULL 
 AND `is_primary`=1

Update Sort Name if showing email when Last exists

Code: [Select]

UPDATE civicrm_contact set sort_name = CONCAT_WS (' ,', last_name, first_name) 
WHERE sort_name like '%@%' 
AND last_name is not null;

Capitalise Names

If you only want to capitalize first character of last word and leave everything else as it is:

Code: [Select]

select name,concat(
replace(name,substring_index(name,'\ ',-1),''),
ucase(left(substring_index(name,'\ ',-1),1)),
mid(substring_index(name,'\ ',-1),2)
) as name2 from names

Code: [Select]

select name,concat(
ucase(left(replace(name,substring_index(name,'\ ',-1),''),1)),
lcase(mid(replace(name,substring_index(name,'\ ',-1),''),2)),
ucase(left(substring_index(name,'\ ',-1),1)),
lcase(mid(substring_index(name,'\ ',-1),2))
) as name2 from names

xavier · October 13, 2010, 11:18:05 pm

Hi,

Thought about it, and while I still think that's basically a too complex problem to solve, I think we can come up with a better solution.

On the contact create form or profiles, when you leave the field, check it it's all lower or all UPPER. If this is the case. capitalize the first letter.

Meaning if van den hallen types it like that, will be Van den hallen. If he is not happy with it, he can change to van den Hallen, or Van den Hallen, or whatever. as soon as there is a mix of upper and lower, that's good enough.

Probably needs to be applied as well for the import, and an option to the API? Let's focus on the UI, and get that working on the profile and create contact.

Should the same rule be applied to the org as well?

And will mostly work in latin char, no idea how it works in chinese (seems to remember Jimmy was saying that's always upper for a last name), nor other languages.

X+

jimmyjam · October 13, 2010, 11:40:45 pm

Thanks for sharing your work, Peter! This is super helpful.

Best regards,
James

jimmyjam · October 21, 2010, 04:17:42 pm

Hi Peter,

I ran the query you suggested for updating the Sort Name field:

Code: [Select]

UPDATE civicrm_contact set sort_name = CONCAT_WS (' ,', last_name, first_name) 
WHERE sort_name like '%@%' 
AND last_name is not null;

I am getting back this error (in phpMyAdmin):

Quote

#1305 - FUNCTION jimmyjam_civi1.CONCAT_WS does not exist

Any suggestions?

Thank you,
James

petednz · October 21, 2010, 06:11:41 pm

Sorry - it worked for us - I didn't construct it - we used phpmyadmin too - maybe different version?

jimmyjam · October 21, 2010, 08:30:53 pm

It sounds like "CONCAT_WS" may be a named function stored somewhere in the database? Or perhaps your database is configured to allow functions to be run and mine isn't. Unfortunately, I am a total MySQL noob.

Thanks,
James

CiviCRM Community Forums (archive)

News:

Author Topic: Capitalising Last and First and getting Sort name and Display Name to update (Read 3593 times)

petednz

Capitalising Last and First and getting Sort name and Display Name to update

xavier

Re: Capitalising Last and First and getting Sort name and Display Name to update

petednz

Re: Capitalising Last and First and getting Sort name and Display Name to update

petednz

Re: Capitalising Last and First and getting Sort name and Display Name to update

Donald Lobo

Re: Capitalising Last and First and getting Sort name and Display Name to update

petednz

Re: Capitalising Last and First and getting Sort name and Display Name to update

xavier

Re: Capitalising Last and First and getting Sort name and Display Name to update

petednz

Re: Capitalising Last and First and getting Sort name and Display Name to update

jimmyjam

Re: Capitalising Last and First and getting Sort name and Display Name to update

petednz

Re: Capitalising Last and First and getting Sort name and Display Name to update

xavier

Re: Capitalising Last and First and getting Sort name and Display Name to update

jimmyjam

Re: Capitalising Last and First and getting Sort name and Display Name to update

jimmyjam

Re: Capitalising Last and First and getting Sort name and Display Name to update

petednz

Re: Capitalising Last and First and getting Sort name and Display Name to update

jimmyjam

Re: Capitalising Last and First and getting Sort name and Display Name to update