Issue with +LEFT(xx) if a value IS NULL

• j.clay 47557

  Right there with Babe

  Points: 720

  April 10, 2019 at 12:10 pm

  #3089534

  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

• @Taps

  SSC-Addicted

  Points: 474

  April 10, 2019 at 1:05 pm

  #3090384

  You can use ISNULL to replace NULL with blank - eg

  SELECT (LEFT(ISNULL( middlename, '') , 1) )

• Michael L John

  One Orange Chip

  Points: 25717

  April 10, 2019 at 2:01 pm

  #3091480

  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.

  https://docs.microsoft.com/en-us/sql/t-sql/statements/set-concat-null-yields-null-transact-sql?view=sql-server-2017

  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/

• j.clay 47557

  Right there with Babe

  Points: 720

  April 11, 2019 at 8:34 am

  #3117129

  Thank you both, much appreciated.

• DesNorton

  SSC-Insane

  Points: 22604

  April 11, 2019 at 8:55 am

  #3117742

  @Taps wrote:

  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]

  • This reply was modified 4 months, 2 weeks ago by  DesNorton.

  ---

  How to post data/code on a forum to get the best help.

• daniness

  SSCrazy

  Points: 2890

  April 15, 2019 at 8:10 pm

  #3387165

  I believe you could also use COALESCE:

  SELECT (LEFT(COALESCE( middlename, ”) , 1) )

   

• Chris Harshman

  SSC-Forever

  Points: 41703

  April 15, 2019 at 8:39 pm

  #3387860

  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]

  https://docs.microsoft.com/en-us/sql/t-sql/functions/concat-transact-sql?view=sql-server-2017&viewFallbackFrom=sql-server-2014

   

• j.clay 47557

  Right there with Babe

  Points: 720

  April 17, 2019 at 11:38 am

  #3424471

  Thanks everyone, much appreciated.