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
Name | FName | MName | LName |
---|---|---|---|
dbo.Person | Name_First | Name_Middle | Name_Last |
dbo.ResidencyApplication | FName | LName |
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
Getting Student Name With Highest Total Mark in SQL
Convert Utc Milliseconds to Datetime in SQL Server
Sql Server Function to Return Minimum Date (January 1, 1753)
Er_Access_Denied_Error: Access Denied for User ''@'Localhost' (Using Password: No)
How to Get Column Name Based on Row Value in SQL Server
Query to Calculate Average of Employee Salaries Working Under Manager in SQL
How to Extract Part of a String in Hive
Max and Min Sal With Employee Name in One Query
Sql71501 - How to Get Rid of This Error
How to See Query History in SQL Server Management Studio
Remove Decimal Values Using SQL Query
How to Return the Most Recent Logtime for Each Sku
Add Single Quotes to Results in a Column from a SQL Query
How to Convert from Blob to Text in MySQL
Selecting Same Column Twice from a Single Table But With Different Conditions
How to Import CSV Data into a Table Without Knowing the Columns of the Csv
List All Employee'S Names and Their Managers by Manager Name Using an Inner Join