CODEWITHSUNDEEP
sqlsql-server-2005
atrljoe
atrljoe

Reputation: 8151

SQL Combine Two Columns in Select Statement

If I have a column that is Address1 and Address2 in my database, how do I combine those columns so that I could perform operations on it only in my select statement, I will still leave them separate in the database. I would like to be able to do this

WHERE completeaddress LIKE '%searchstring%'

Where completedaddress is the combination of Address1 and Address2. searchstring would be like the data they searched for. So if they had '123 Center St' in Address1 and 'Apt 3B' in Address2, how would I have it select it if the searchstring was 'Center St 3B' Is this possible with SQL?

Upvotes: 33

Views: 298513

Answers (5)

user3003449
user3003449

Reputation: 11

SELECT StaffId,(Title+''+FirstName+''+LastName) AS FullName 
FROM StaffInformation

Where do you write with in the brackets this will be appear in the one single column. Where do you want a dot into the middle of the Title and First Name write syntax below,

SELECT StaffId,(Title+'.'+FirstName+''+LastName) AS FullName 
FROM StaffInformation

These syntax works with MS SQL Server 2008 R2 Express Edition.

Upvotes: 1

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55489

If your address1 = '123 Center St' and address2 = 'Apt 3B' then even if you combine and do a LIKE, you cannot search on searchstring as 'Center St 3B'. However, if your searchstring was 'Center St Apt', then you can do it using -

WHERE (address1 + ' ' + address2) LIKE '%searchstring%'

Upvotes: 2

pavanred
pavanred

Reputation: 13803

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'.

Upvotes: 50

anothershrubery
anothershrubery

Reputation: 21003

In MySQL you can use:

SELECT CONCAT(Address1, " ", Address2)
WHERE SOUNDEX(CONCAT(Address1, " ", Address2)) = SOUNDEX("Center St 3B")

The SOUNDEX function works similarly in most database systems, I can't think of the syntax for MSSQL at the minute, but it wouldn't be too far away from the above.

Upvotes: 17

Shane Delmore
Shane Delmore

Reputation: 1575

If you don't want to change your database schema (and I would not for this simple query) you can just combine them in the filter like this: WHERE (Address1 + Address2) LIKE '%searchstring%'

Upvotes: 0

Related Questions