Clever Geek Handbook

SQL Server Full Text Search - Get Full Field Value

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 do a full text search like:

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

Is it possible to go back

  • the name of the column containing the set value (in this example it will be "CompanyName")
  • the complete column value that contains the based value (in this example, it would be "El Salvador"
+3


source to share


1 answer


I can suggest the following:



 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')
+1


source






More articles:


All Articles