locked
How to code a database view with the middle initial goes in the first name field? RRS feed

  • Question

  • User-830563764 posted

    How to code a database view with the middle initial goes in the first name field?
    firstname + ' ' + ?midname? AS FRSTNAME

    Monday, September 19, 2016 8:49 PM

Answers

  • User-595703101 posted

    Hi wonjartran,

    I used following script on AdventureWorks sample database

    select top 100 
    	isnull(FirstName,'') + isnull(Space(1) + Substring(MiddleName,1,1),'') as FirstName,
    	FirstName,
    	MiddleName
    from Person.Person

    And other option is using the SQL CONCAT() function introduced with SQL Server 2012 to deal with NULL values easier

    SELECT top 10
    	CONCAT(FirstName, Space(1) + Substring(MiddleName,1,1)) FirstName,
    	FirstName, MiddleName
    FROM Person.Person 

    I hope it helps

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 23, 2016 5:17 AM

All replies

  • User-2057865890 posted

    Hi wonjartran,

    Check your data in your column and show us table structure with data.

    Best Regards,

    Chris

    Wednesday, September 21, 2016 6:03 AM
  • User-595703101 posted

    Hi wonjartran,

    I used following script on AdventureWorks sample database

    select top 100 
    	isnull(FirstName,'') + isnull(Space(1) + Substring(MiddleName,1,1),'') as FirstName,
    	FirstName,
    	MiddleName
    from Person.Person

    And other option is using the SQL CONCAT() function introduced with SQL Server 2012 to deal with NULL values easier

    SELECT top 10
    	CONCAT(FirstName, Space(1) + Substring(MiddleName,1,1)) FirstName,
    	FirstName, MiddleName
    FROM Person.Person 

    I hope it helps

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 23, 2016 5:17 AM