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

  • j.clay 47557

    Mr or Mrs. 500

    Points: 563

    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: 421

    You can use ISNULL to replace NULL with blank – eg

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

  • Michael L John

    One Orange Chip

    Points: 25545

    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

    Mr or Mrs. 500

    Points: 563

    Thank you both, much appreciated.

  • DesNorton

    SSC-Insane

    Points: 22344

    @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 1 week ago by  DesNorton.


    How to post data/code on a forum to get the best help.[/url]
    Make sure that you include code in the appropriate IFCode tags.

  • daniness

    SSCrazy

    Points: 2592

    I believe you could also use COALESCE:

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

     

  • Chris Harshman

    SSC-Forever

    Points: 41584

    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

    Mr or Mrs. 500

    Points: 563

    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