SQL在Select语句中合并了两列

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

如果我的数据库中有一个Address1和Address2的列，我如何将这些列组合起来，以便只在select语句中对其执行操作，我仍然将它们单独放在数据库中。我希望能做到这一点

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?

复合地址是Address1和Address2的组合。searchstring就像他们搜索的数据一样。那么，如果他们在Address1中有“123 Center St”，在Address2中有“Apt 3B”，如果searchstring是“Center St 3B”，我怎么让它选择它呢?

5 个解决方案

#1

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 -

为了防止在我们将address1附加到address2时创建一个复合字，您可以使用这个-

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

因此，“123中心St”和“Apt 3B”将不会是“123中心StApt 3B”，而是“123中心StApt 3B”。

#2

In MySQL you can use:

在MySQL中，您可以使用:

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.

在大多数数据库系统中，SOUNDEX函数的工作原理与此类似，我一时想不起MSSQL的语法，但它离上面的语法并不远。

#3

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 -

如果你的address1 = '123 Center St'和address2 = 'Apt 3B'，那么即使你合并并执行类似的操作，你也不能在searchstring上搜索'Center St 3B'。但是，如果您的搜索字符串是“Center St Apt”，那么您可以使用-

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

#4

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.

这些语法适用于MS SQL Server 2008 R2 Express Edition。

#5

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

如果您不希望更改数据库模式(对于这个简单的查询，我也不希望更改)，您可以将它们组合到过滤器中，如:WHERE (Address1 + Address2)，如'%searchstring%'

#1