Post reply

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

  • 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

  • April 10, 2019 at 1:05 pm

    #3090384

    You can use ISNULL to replace NULL with blank - eg

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

  • 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/

  • April 11, 2019 at 8:34 am

    #3117129

    Thank you both, much appreciated.

  • 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 5 years, 7 months ago by  DesNorton.

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

  • April 15, 2019 at 8:10 pm

    #3387165

    I believe you could also use COALESCE:

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

     

  • 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

     

  • April 17, 2019 at 11:38 am

    #3424471

    Thanks everyone, much appreciated.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply