Post reply

How to get full name of the employer by getting rid of null values in between

  • October 14, 2016 at 1:12 pm

    #318149

    HI, I have a table name EmployeeNames

    ID, FirstName, MiddleName, LastName

    1, Sam, NULL, NULL

    2, NULL,Todd, Tanzan

    3, NULL,NULL, Sara

    4, Ben,Parker, NULL

    5, James,Paul, Bond

    6, Tim,NULL, Crook

    I need to get out put as follows

    ID, FullName

    1 Sam

    2 Todd Tanzan

    3 Sara

    4 Ben Parker

    5 James Paul Bond

    6 Tim Crook

    Output should get rid of null values.

  • October 14, 2016 at 1:22 pm

    #1906669

    here is how I do this

    replace(coalesce(FirstName,'')+' '+coalesce(MiddleName,'')+' '+coalesce(LastName,''), ' ', ' ')

  • October 14, 2016 at 1:26 pm

    #1906671

    marc.corbeel (10/14/2016)

    here is how I do this

    best include trims also

    ltrim(rtrim(replace(coalesce(FirstName,'')+' '+coalesce(MiddleName,'')+' '+coalesce(LastName,''), ' ', ' ')))

  • October 14, 2016 at 1:40 pm

    #1906677

    marc.corbeel (10/14/2016)

    here is how I do this

    replace(coalesce(FirstName,'')+' '+coalesce(MiddleName,'')+' '+coalesce(LastName,''), ' ', ' ')

    What is the point of the replace here? You are replacing all single spaces with a single space. It is not going to produce the desired output.

    Something like this would more closely capture what the OP is after.

    coalesce(FirstName + ' ', '') + coalesce(MiddleName + ' ', '' + coalesce(LastName + ' ', '')

    Here is a full working example.

    create table #something

    (

    ID int

    , FirstName varchar(10)

    , MiddleName varchar(10)

    , LastName varchar(10)

    )

    insert #something

    select 1, 'Sam', NULL, NULL union all

    select 2, NULL, 'Todd', 'Tanzan' union all

    select 3, NULL, NULL, 'Sara' union all

    select 4, 'Ben', 'Parker', NULL union all

    select 5, 'James', 'Paul', 'Bond' union all

    select 6, 'Tim', NULL, 'Crook'

    select marcs_version = replace(coalesce(FirstName,'')+' '+coalesce(MiddleName,'')+' '+coalesce(LastName,''), ' ', ' ')

    , seans_version = coalesce(FirstName + ' ', '') + coalesce(MiddleName + ' ', '') + coalesce(LastName + ' ', '')

    from #something

    drop table #something

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • October 14, 2016 at 1:44 pm

    #1906679

    Sean Lange (10/14/2016)

    marc.corbeel (10/14/2016)

    here is how I do this

    replace(coalesce(FirstName,'')+' '+coalesce(MiddleName,'')+' '+coalesce(LastName,''), ' ', ' ')

    What is the point of the replace here? You are replacing all single spaces with a single space. It is not going to produce the desired output.

    Something like this would more closely capture what the OP is after.

    coalesce(FirstName + ' ', '') + coalesce(MiddleName + ' ', '' + coalesce(LastName + ' ', '')

    Here is a full working example.

    create table #something

    (

    ID int

    , FirstName varchar(10)

    , MiddleName varchar(10)

    , LastName varchar(10)

    )

    insert #something

    select 1, 'Sam', NULL, NULL union all

    select 2, NULL, 'Todd', 'Tanzan' union all

    select 3, NULL, NULL, 'Sara' union all

    select 4, 'Ben', 'Parker', NULL union all

    select 5, 'James', 'Paul', 'Bond' union all

    select 6, 'Tim', NULL, 'Crook'

    select marcs_version = replace(coalesce(FirstName,'')+' '+coalesce(MiddleName,'')+' '+coalesce(LastName,''), ' ', ' ')

    , seans_version = coalesce(FirstName + ' ', '') + coalesce(MiddleName + ' ', '') + coalesce(LastName + ' ', '')

    from #something

    drop table #something

  • October 14, 2016 at 1:46 pm

    #1906680

    Sean Lange (10/14/2016)

    marc.corbeel (10/14/2016)

    here is how I do this

    replace(coalesce(FirstName,'')+' '+coalesce(MiddleName,'')+' '+coalesce(LastName,''), ' ', ' ')

    What is the point of the replace here? You are replacing all single spaces with a single space. It is not going to produce the desired output.

    Something like this would more closely capture what the OP is after.

    coalesce(FirstName + ' ', '') + coalesce(MiddleName + ' ', '' + coalesce(LastName + ' ', '')

    Here is a full working example.

    create table #something

    (

    ID int

    , FirstName varchar(10)

    , MiddleName varchar(10)

    , LastName varchar(10)

    )

    insert #something

    select 1, 'Sam', NULL, NULL union all

    select 2, NULL, 'Todd', 'Tanzan' union all

    select 3, NULL, NULL, 'Sara' union all

    select 4, 'Ben', 'Parker', NULL union all

    select 5, 'James', 'Paul', 'Bond' union all

    select 6, 'Tim', NULL, 'Crook'

    select marcs_version = replace(coalesce(FirstName,'')+' '+coalesce(MiddleName,'')+' '+coalesce(LastName,''), ' ', ' ')

    , seans_version = coalesce(FirstName + ' ', '') + coalesce(MiddleName + ' ', '') + coalesce(LastName + ' ', '')

    from #something

    drop table #something

    I replace a double space by a single one, because if the middle name is empty, you will have two spaces next to each other.

  • October 14, 2016 at 1:51 pm

    #1906682

    marc.corbeel (10/14/2016)

    Sean Lange (10/14/2016)

    marc.corbeel (10/14/2016)

    here is how I do this

    replace(coalesce(FirstName,'')+' '+coalesce(MiddleName,'')+' '+coalesce(LastName,''), ' ', ' ')

    What is the point of the replace here? You are replacing all single spaces with a single space. It is not going to produce the desired output.

    Something like this would more closely capture what the OP is after.

    coalesce(FirstName + ' ', '') + coalesce(MiddleName + ' ', '' + coalesce(LastName + ' ', '')

    Here is a full working example.

    create table #something

    (

    ID int

    , FirstName varchar(10)

    , MiddleName varchar(10)

    , LastName varchar(10)

    )

    insert #something

    select 1, 'Sam', NULL, NULL union all

    select 2, NULL, 'Todd', 'Tanzan' union all

    select 3, NULL, NULL, 'Sara' union all

    select 4, 'Ben', 'Parker', NULL union all

    select 5, 'James', 'Paul', 'Bond' union all

    select 6, 'Tim', NULL, 'Crook'

    select marcs_version = replace(coalesce(FirstName,'')+' '+coalesce(MiddleName,'')+' '+coalesce(LastName,''), ' ', ' ')

    , seans_version = coalesce(FirstName + ' ', '') + coalesce(MiddleName + ' ', '') + coalesce(LastName + ' ', '')

    from #something

    drop table #something

    I replace a double space by a single one, because if the middle name is empty, you will have two spaces next to each other.

    Try running the example I posted. Your logic will return incorrect results. Things like leading and trailing spaces. And the code you posted does not replace double spaces. As posted it replaces ' ' with ' '. In other words, as posted the replace doesn't do anything. And even if it did replace ' ' with ' ' you would still have issues with leading and trailing spaces.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • October 14, 2016 at 1:53 pm

    #1906684

    I clearly typed a space in stead of two... sorry

    ltrim(rtrim(replace(coalesce(FirstName,'')+' '+coalesce(MiddleName,'')+' '+coalesce(LastName,''), ' ', ' ')))

  • October 14, 2016 at 1:54 pm

    #1906685

    This is strange...

    when I copy from my SQL studio Manager to this forum, each time the double space is replaced by single one

    Anyone knows this issue???

  • October 14, 2016 at 1:55 pm

    #1906686

    Thank you all.

    I tried this It also works.

    select Id, ltrim((isnull(Firstname,'') + ' ' + isnull(Middlename,'') + ' ' + isnull(Lastname,''))) as FullName from EmployeeName

  • October 14, 2016 at 1:55 pm

    #1906687

    one more try

    ltrim(rtrim(replace(coalesce(FirstName,'')+' '+coalesce(MiddleName,'')+' '+coalesce(LastName,''), ' ', ' ')))

  • October 14, 2016 at 1:58 pm

    #1906690

    Forum admin... this is creepy!

    I paste a peace of code text with a double space in it, in the topic reply text box, and once saved, the double space is replaced by a single one...

  • October 14, 2016 at 1:59 pm

    #1906691

    marc.corbeel (10/14/2016)

    Forum admin... this is creepy!

    I paste a peace of code text with a double space in it, in the topic reply text box, and once saved, the double space is replaced by a single one...

    Put in a code box. You can use the IFCode shortcuts on the left when you are posting.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • October 14, 2016 at 2:02 pm

    #1906692

    Sean Lange (10/14/2016)

    marc.corbeel (10/14/2016)

    Forum admin... this is creepy!

    I paste a peace of code text with a double space in it, in the topic reply text box, and once saved, the double space is replaced by a single one...

    Put in a code box. You can use the IFCode shortcuts on the left when you are posting.

    okay, didn't know that, thank you.

  • October 14, 2016 at 2:02 pm

    #1906693

    swarun999 (10/14/2016)

    Thank you all.

    I tried this It also works.

    select Id, ltrim((isnull(Firstname,'') + ' ' + isnull(Middlename,'') + ' ' + isnull(Lastname,''))) as FullName from EmployeeName

    This captures most of it but you will still have trailing spaces. Look at "Sam" or any others that have a NULL after first name. The way I put mine together was using the fact that when you add NULL and anything else you get NULL. If you prefer ISNULL over coalesce just replace the keyword in my solution. It will produce the desired output with about as simple as it can get.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 15 posts - 1 through 15 (of 19 total)

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