SQL Combine Two Columns in Select Statement

SQL Combine Two Columns in Select Statement

I think this is what you are looking for -

select Address1+Address2 as CompleteAddress from YourTable
where Address1+Address2 like '%YourSearchString%'

To prevent a compound word being created when we append address1 with address2, you can use this -

select Address1 + ' ' + Address2 as CompleteAddress from YourTable 
where Address1 + ' ' + Address2 like '%YourSearchString%'

So, '123 Center St' and 'Apt 3B' will not be '123 Center StApt 3B' but will be '123 Center St Apt 3B'.

MySQL combine two columns into one column

My guess is that you are using MySQL where the + operator does addition, along with silent conversion of the values to numbers. If a value does not start with a digit, then the converted value is 0.

So try this:

select concat(column1, column2)

Two ways to add a space:

select concat(column1, ' ', column2)
select concat_ws(' ', column1, column2)

how to concat two columns into one with the existing column name in mysql?

As aziz-shaikh has pointed out, there is no way to suppress an individual column from the * directive, however you might be able to use the following hack:

SELECT CONCAT(c.FIRSTNAME, ',', c.LASTNAME) AS FIRSTNAME,
       c.*
FROM   `customer` c;

Doing this will cause the second occurrence of the FIRSTNAME column to adopt the alias FIRSTNAME_1 so you should be able to safely address your customised FIRSTNAME column. You need to alias the table because * in any position other than at the start will fail if not aliased.

Hope that helps!

Combining Multiple Columns into One based off another field

Use this :

Select MyId,Col1 as CombininedCol from YourTable where Col1 is not null
Union All
Select MyId,Col2 as CombininedCol from YourTable where Col2 is not null
Union All
Select MyId,Col3 as CombininedCol from YourTable where Col3 is not null
Union All
Select MyId,Col4 as CombininedCol from YourTable where Col4 is not null
order by MyId
 

Tip: The UNION ALL command combines the result set of two or more SELECT statements (allows duplicate values).

Each select separately get the two columns of MyID and the Cole 1,2, etc., and finally returns them as two columns.

Select 2 columns in one and combine them

Yes, just like you did:

select something + somethingElse as onlyOneColumn from someTable

If you queried the database, you would have gotten the right answer.

What happens is you ask for an expression. A very simple expression is just a column name, a more complicated expression can have formulas etc in it.

MySQL SELECT AS combine two columns into one

If both columns can contain NULL, but you still want to merge them to a single string, the easiest solution is to use CONCAT_WS():

SELECT FirstName AS First_Name
     , LastName AS Last_Name
     , CONCAT_WS('', ContactPhoneAreaCode1, ContactPhoneNumber1) AS Contact_Phone 
  FROM TABLE1

This way you won't have to check for NULL-ness of each column separately.

Alternatively, if both columns are actually defined as NOT NULL, CONCAT() will be quite enough:

SELECT FirstName AS First_Name
     , LastName AS Last_Name
     , CONCAT(ContactPhoneAreaCode1, ContactPhoneNumber1) AS Contact_Phone 
  FROM TABLE1

As for COALESCE, it's a bit different beast: given the list of arguments, it returns the first that's not NULL.

SQL query combine two columns that contain different data type

Finally I figured out how to solve the issue by implementing the following syntax:

Select  '[' + CAST (REQ.RQ_USER_TEMPlATE_06 AS VARCHAR(5000)) + ']' + req.rq_req_id AS 'MSR ID / Version' FROM REQ WHERE rq_type_id = '107'

---

Related Topics