SQL Server Full-Text search - get total field value

Question

Let's say I have the following table "Addresses":

+----+-------------+---------------+------------------+
| ID | CompanyName |    Street     |     City         |
+----+-------------+---------------+------------------+
|  1 | Salvador    | Hollywood 123 | Paradise City    |
|  2 | Zer0        | Avenue 34     | Opportunity City |
+----+-------------+---------------+------------------+

If I make a full-text search like:

SELECT * FROM Addresses WHERE CONTAINS(*, 'Salv')

Is it possible to get back

• the name of the column, which contains the founded value (in this example it would be "CompanyName")
• the full value of the column, which contains the founded value (in this example it would be "Salvador"

Answer 1

I can suggest this:

 SELECT 
      *,
      CASE WHEN CONTAINS(CompanyName, 'Salv') THEN 'CompanyName'
           WHEN CONTAINS(Street, 'Salv') THEN 'Street'
           WHEN CONTAINS(City, 'Salv') THEN 'City'
      END As ColumnName,
      CASE WHEN CONTAINS(CompanyName, 'Salv') THEN CompanyName
           WHEN CONTAINS(Street, 'Salv') THEN Street
           WHEN CONTAINS(City, 'Salv') THEN City
      END As FullText
 FROM Addresses 
 WHERE CONTAINS(*, 'Salv')