How to Combine First Name, Middle Name and Last Name in SQL Server

Concatenate first name, last name and middle name with comma

You could use a CASE expression:

SELECT MAX(CASE WHEN ISNULL(FirstName, '') = '' AND
ISNULL(MiddleName, '') = '' AND
ISNULL(LastName, '') = ''
THEN 'N/A'
ELSE LTRIM(RTRIM((ISNULL(LastName,'') + ', ' +
ISNULL(FirstName,'') + ', ' +
ISNULL(MiddleName,''))))
END) AS FullName
FROM yourTable
...

How do I get first name and last name as whole name in a MYSQL query?

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws

SELECT CONCAT_WS(" ", `first_name`, `last_name`) AS `whole_name` FROM `users`

concatenate first name middle name and last name if found in the same table

Firstly, lets match on multiple criteria, I would suggest that %first% is not enough, it won't match FName for instance, so lets include %name% in the search:

In fact, based on your example data, %name% should be enough!

SELECT TABLE_SCHEMA+'.'+TABLE_NAME, COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%first%' OR COLUMN_NAME LIKE '%name%'

If you are happy that all the tables are represented in this list, then we can move on to the next step...

We can discover the optimal column names by applying some simple CASE logic and grouping by the table name:

This effectively a manual PIVOT over the previous resultset

;WITH TableSchema as 
(
SELECT TABLE_SCHEMA+'.'+TABLE_NAME as [Name], COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA + '.' + TABLE_NAME
IN (
SELECT DISTINCT TABLE_SCHEMA+'.'+TABLE_NAME as [Name]
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%first%' OR COLUMN_NAME LIKE '%name%'
)
)
SELECT t.[Name]
, MIN(FName) as [FName]
, MIN(MName) as [MName]
, MIN(LName) as [LName]
FROM TableSchema t
CROSS APPLY ( SELECT
CASE WHEN COLUMN_NAME IN ('fname','firstname','first','name_first') THEN COLUMN_NAME END as [FName]
,CASE WHEN COLUMN_NAME IN ('mname','middleName','name_middle') THEN COLUMN_NAME END as [MName]
,CASE WHEN COLUMN_NAME IN ('lname','lastname','name_last') THEN COLUMN_NAME END as [LName]
) as NameColumns
GROUP BY t.[Name]
HAVING COUNT(FName) > 0

I've added some additional cases so my database names came up

























NameFNameMNameLName
dbo.PersonName_FirstName_MiddleName_Last
dbo.ResidencyApplicationFNameLName

How to combine First, Middle, Last names in a single statement?

If you're on a recent version of SQL Server, you can simply use CONCAT_WS:

SELECT CONCAT_WS(' ',Firstname, MiddleInitial, LastName
FROM dbo.YourTable;

If you're on an older version, you can put the "delimiter" (in this case a space (' ')) before each value, and strip the first one out with STUFF:

SELECT STUFF(CONCAT(' ' + Firstname, ' ' + MiddleInitial, ' ' + LastName),1,1,'')
FROM dbo.YourTable;

If a value might be blank, really you should be fixing your design and have a check constraint that doesn't allow '' values, but you could wrap each value with a NULLIF:

SELECT STUFF(CONCAT(' ' + NULLIF(Firstname,''), ' ' + NULLIF(MiddleInitial,''), ' ' + NULLIF(LastName,'')),1,1,'')
FROM dbo.YourTable;

Combine First, Middle Initial, Last name and Suffix in T-SQL (No extra spaces)

Assuming that all columns could be nullable, you can do something like:

RTrim(Coalesce(FirstName + ' ','') 
+ Coalesce(MiddleInitial + ' ', '')
+ Coalesce(LastName + ' ', '')
+ Coalesce(Suffix, ''))

This relies on the fact that adding to a NULL value yields a NULL.

how to combine firstname and lastname in SQL and search with LIKE

You can use LIKE with concatenated column values as below

WHERE 
CC.FirstName + ' ' + CC.LastName LIKE 's%'

BUT it only search for name starting with 's', i would like to search
name starting, ending, contains conditions as well. how can i go about
that. please help

Then you have to use % before and after search string like below

WHERE 
CC.FirstName + ' ' + CC.LastName LIKE '%s%'

Combine first and last name and update into full name column for each row

This UPDATES FullName Colum that are blank or have NULL values.

UPDATE Names
SET FullName = ISNULL(FirstName + ' ','') + ISNULL(LastName,'')
WHERE ISNULL(FullName,'') = ''

EDIT

UPDATE Names
SET FullName = FirstName + ' ' + LastName
WHERE ISNULL(FullName,'') = '' AND ISNULL(FirstName,'') <> '' AND ISNULL(LastName,'') <> ''


Related Topics



Leave a reply



Submit