April 10, 2019 at 12:10 pm
Hi everyone
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]
INTO TEMPENVSALUTATION
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.
Many thanks
Jon
April 10, 2019 at 1:05 pm
You can use ISNULL to replace NULL with blank - eg
SELECT (LEFT(ISNULL( middlename, '') , 1) )
April 10, 2019 at 2:01 pm
This is the default setting in SQL Server. When you concat a null, it will return null.
The behavior can be changed, however this setting is deprecated. Like @Taps suggested, the ISNULL function will fix this for you.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 11, 2019 at 8:34 am
Thank you both, much appreciated.
April 11, 2019 at 8:55 am
You can use ISNULL to replace NULL with blank - eg
SELECT (LEFT(ISNULL( middlename, '') , 1) )
You may want to wrap the space in the ISNULL as well, to ensure even spacing of the initials
co.title+ISNULL(’ ‘+LEFT(co.firstname, 1), '')+ISNULL(’ ‘+LEFT(co.otherinitial, 1), '')+’ ‘+co.keyname [NEW]
April 15, 2019 at 8:10 pm
I believe you could also use COALESCE:
SELECT (LEFT(COALESCE( middlename, ”) , 1) )
April 15, 2019 at 8:39 pm
SQL Server 2012 introduced the new CONCAT function which will automatically handle the NULL values for you, and makes the resulting line of code look a little cleaner:
CONCAT(title + ' ', LEFT(co.firstname, 1), ' ', LEFT(co.otherinitial, 1) + ' ', co.lastname) AS [NEW]
April 17, 2019 at 11:38 am
Thanks everyone, much appreciated.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply