Full text search

Full text search with SQL? such as i want to find a specific word in any colum of the table

The "brute force" method is to use use the LIKE operator against any of the fields to be searched. This will be relatively expensive - but probably good enough in many cases. The term to search for should be quoted and placed within two wild cards.

You should construct the string literal in some scripting language - don't forget to quote it.

SELECT name FROM scott.cia
WHERE LOWER(name) LIKE '%the%'

SELECT name FROM gisq.cia
WHERE name LIKE '%the%'

SELECT name FROM gisq.cia
WHERE name LIKE '%the%'

SELECT name FROM cia
WHERE name LIKE '%the%'

SELECT name FROM gisq.cia
WHERE name LIKE '%the%'

The LIKE command is case sensitive - to do a case insensitive search you should force the searched field and the search term into lower (or upper) case. Without this we would miss "Bahamas, The"

The LIKE operator is case insensitive.

The LIKE operator is case insensitive.

String comparison in Access is always case insensitive.

SELECT Operations

Concatenate Columns

Like

Union

Apostrophe

Full text search

Column name

Equi Join

Column name with spaces

NULL