SQL SERVER : Select Query - Not So Simple Query

I have a "Career" table in SQL SERVER which has foll. data:

P_Id FirstName
1 maid
2 maids singapore
3 Fashion Designer
4 Kari
5 Designers
6 Fashion

Now we want to Search the FirstName with foll. scenario / examples

Example 1 : Search word : "maids"
Should display records 1,2

Example 2 : Search word : "singapore"
Should display record number : 2

Example 3 : Search word : "Designer"
Should display record number : 3, 5

Example 4 : Search word : "Fashion"
Should display record number : 3, 6

Example 5 : Search word : "housemaids"
Should display records 1,2

 

Examples 1 and 5 can't be done without altering search word, others can be achieved by

 

as MattCA said!

+1 MAttCA

 

IS there any other possibility by breaking the words into characters or some intelligent way ? I see many of the site does that ?

 

words in the search area ? or words saved in the db ?

 

Words in the search criteria

 

You can always trim last 1-2 characters from a word to get more results, e.g.:
search = 'games' -1 = 'game'
search 'played' -1 = 'playe' (matches player, players etc)
search 'played' -2 = 'play'

You could do it in PHP with substr() function.

 

I thought its possible using SQL SERVER - T SQL Queries

 

but that does not mean it will always return desired solutions , even it may return trash solutions,
for example:
a user search for joe,
if we trim last 2 chars from user search field ,
that mean we will be searching in the database for like 'j%' ,
results will return for example:
joe
jonathan
jihad
jenny
jessy

lots of trash results

 

look up: mysql fulltext search, sphinx, solr/lucene or use mysql regex to put word boundary.

SELECT P_Id, FirstName FROM employees WHERE FirstName LIKE REGEXP '[[:<:]]Don[[:>:]]';

Code (markup):

Don Carlito <-- match
Donovan Joe <-- not match
Dono Kasi Indro <-- not match

cmiiw

 

If you want to manipulate the string with SQL, you can use a function like this: