CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Gitz
Gitz

Reputation: 820

Customized search with special characters

I am creating a key-wording module where I want to search data using the comma separated words.And the search is categorized into comma , and minus -. Take a look on the example what I exactly want to do is

I have a main table name tbl_main in SQL

AS_ID   KWD
1   Man,Businessman,Business,Office,confidence,arms crossed
2   Man,Businessman,Business,Office,laptop,corridor,waiting
3   man,business,mobile phone,mobile,phone
4   Welcome,Greeting,beautiful,bride,celebration,wedding,woman,happiness
5   beautiful,bride,wedding,woman,happiness,mobile phone,talking
6   woman,girl,Digital Tablet,working,sitting,online
7   woman,girl,Digital Tablet,working,smiling,happiness,hand on chin

If search text is = Man,Businessman then result AS_ID is =1,2

If search text is = Man,-Businessman then result AS_ID is =3

If search text is = woman,girl,-Working then result AS_ID is =4,5

What is the best why to do this, Help is much appreciated.Thanks in advance

Upvotes: 1

Views: 56

Answers (1)

Ionic
Ionic

Reputation: 3935

Well just to mention. This isn't a good data structure at all. You should split all categories into separate rows which will improve your database in speed and even in the size.

Here an example how I would do this.

Table AS:

AS_ID (PK)
... your additional field...

Table Categories:

CAT_ID (PK)
CAT_NAME (example: Man)

Table AS_Cats

AS_ID (PK) (FK -> Table AS)
CAT_ID (PK) (FK -> Table Categories)

Table AS_Cats holds a combined primary key to avoid duplicate categories for one AS.

If you still really want this data structure, you can take a look at the code for splitting strings into rows. This way you can JOIN and filter for the AS_ID which holds all keys. See an example here.

Upvotes: 1

Related Questions