[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


SQl Server FREETEXTTABLE return matched text.

Posted on 2011-04-19
Medium Priority
Last Modified: 2012-05-11
I have a table say tblResumes


ID  Name   Position   Resume(nText)

I run a FREETEXTTABLE search.

I want to be able to retun only the Text from the column(Resume) which match the keywords in the FREETEXTTABLE search.
Something along the lines of google results.

For Example(This is a sample resume in the "Resume" Column) :

System Admin and database Administrator job involves the following: IMV Project System Implement Schedule Control and Follow up (20 of Sub Systems).    - Cooperate with outsourcing and Global Toyota company to implement system as following items:User Requirement, Infra Structure Design, Database Design, User Design, Unit Test, System Test, User Acceptance Test, System Go-live etc. Application Investigate Skill of Maintenance Phase.  System Administrator: Server Specification Design. Operating System Performance Utilization Monitoring and Analysis to scale out. Application Deployment logging version and check shet control. System Backup and restore. Disaster Recovery Plan Design and Backup Site of Server Room implementation. IIS Web Service configuration and Performance Tuning. Application Component Service configuraiton. Scheduled Task Configuration. N-Tier Application Knowledge and Implementation. SAN Storage administrator (Raid 1+0). LAN/WAN/VPN, Intenet, Intranet Configuration, Proxy/Firewall/Ftp configuration. Implement and Maintaining MS Windows Server Active Directory Infra-Structure.  Database Administrator: Setting Task of Daily, Monthly, Yearly of Database Maintenance Plan such as Automatic Full Backup, Rebuild index, Data Integrity Check:    - Microsoft SQL Server 7  Database Servers (ERP-Powercerv System).    - Microsoft SQL Server 2000 Database Servers (SCMS Phase 1 have 11 Sub System).    - Microsoft SQL Server 2000 Database Servers (SCMS Phase 2 have 5 Sub System).    - Microsoft SQL Server 2000 Database Servers (SIS).    - Microsoft SQL Server 2000 Database Servers (Tool). Microsoft SQL Server Cluster Configuration and Tuning. Database Installation, Standard Configuration and Controlling User Access Configuration. Configuration of Database Performance Tuning. Database Problem Investigate and Solving. Database Performance Utilization Monitoring and Analysis to scale out. Replication Implementation between various database.  Other  

 IF I run a search for 'Microsoft Database Administrator'

Is it possible to return only matched text chunks rather than return all the text from that column. Something like return the 5 preceeding and 5 succeeding words of the macthed keywords.

Is it possibel to do in SQl server or should this be done from the application end?
If yes then please guide me in the right direction.
Question by:isaackhazi
  • 2
LVL 10

Accepted Solution

John Claes earned 2000 total points
ID: 35424945
as Far as i know :
you can't search inside a Ntext field.

what can you do ?
1) convert to nvarchar whe searching ==> Slow because every ntext must be converted

where convert(nvarchar(4000), Resume) like '%YoursearchMatch%'

2) you can extract all the records towards the Client and do the search there because they are all strings==> very slow.

if you need more info ....
poor beggar

Author Comment

ID: 35425575
All I need is a google style result page....

I want to be able to get chunks of text from the entire text field that contain the keywords the user searched for.......

Is anyother approach possible... ??
LVL 10

Expert Comment

by:John Claes
ID: 35431996
as far a I know, You'll have to do the most a Server-Side and not at DB side
I'm realy sorry

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question