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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy