SQl Server FREETEXTTABLE return matched text.

Posted on 2011-04-19
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
    LVL 10

    Accepted Solution

    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
    LVL 8

    Author Comment

    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
    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

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now