I'm using the code below to take someone's title, the first letter of their firstname, the first letter of their middle name and their surname to make a new additional column called NEW in a temporary table.
select distinct co.serialnumber, co.title, co.firstname, co.otherinitial, co.keyname, co.envelopesalutation, co.title+' '+LEFT(co.firstname, 1)+' '+LEFT(co.otherinitial, 1)+' '+co.keyname [NEW]
from contact co
where co.contacttype='Individual' AND co.title in ('Mr','Mrs','Ms','Miss','Mx')
This works if someone has a middle name, but shows the NEW column as NULL if the co.otherinitial column IS NULL (i.e. someone doesn't have a middle name).
So, if someone is called Mr Andrew David Smith then the NEW column will show Mr A D Smith
However, if someone is called Mr Andrew Smith then the NEW column will show NULL (because there's no otherinitial). I would like it to show Mr A Smith.
I'm stuck on how I need to revise my script to effectively ignore the co.otherinitial column if it's NULL when creating the NEW column.